From 68f6e3bcef07a83800864ec04fb2f6e695749729 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Tue, 3 Oct 2023 19:22:40 +0200 Subject: fix(postgres-aggregates): Add info on bench perf --- content/posts/postgres-aggregates/aggregation.txt | 32 +++++++++++------------ 1 file changed, 16 insertions(+), 16 deletions(-) (limited to 'content/posts/postgres-aggregates/aggregation.txt') diff --git a/content/posts/postgres-aggregates/aggregation.txt b/content/posts/postgres-aggregates/aggregation.txt index 19fe5a9..d8c8f18 100644 --- a/content/posts/postgres-aggregates/aggregation.txt +++ b/content/posts/postgres-aggregates/aggregation.txt @@ -1,36 +1,36 @@ QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - GroupAggregate (cost=1.00..280600.32 rows=10000 width=32) (actual time=5.206..2498.463 rows=10000 loops=1) + GroupAggregate (cost=1.00..280612.99 rows=10000 width=32) (actual time=5.465..2682.441 rows=10000 loops=1) Output: products.id, products.name, count(DISTINCT reviews.id), count(DISTINCT orders.id) Group Key: products.id - Buffers: shared hit=1093923 - -> Merge Left Join (cost=1.00..205500.32 rows=10000000 width=24) (actual time=4.720..1482.634 rows=9995979 loops=1) + Buffers: shared hit=1093885 + -> Merge Left Join (cost=1.00..205512.99 rows=10000000 width=24) (actual time=4.829..1538.277 rows=9998159 loops=1) Output: products.id, products.name, reviews.id, orders.id Merge Cond: (products.id = orders.product_id) - Buffers: shared hit=1093923 - -> Merge Left Join (cost=0.58..7362.19 rows=100000 width=20) (actual time=4.706..51.596 rows=100000 loops=1) + Buffers: shared hit=1093885 + -> Merge Left Join (cost=0.58..7362.56 rows=100000 width=20) (actual time=4.809..53.801 rows=100000 loops=1) Output: products.id, products.name, reviews.id Merge Cond: (products.id = reviews.product_id) - Buffers: shared hit=99765 - -> Index Scan using products_pkey on public.products (cost=0.29..387.29 rows=10000 width=16) (actual time=0.010..1.926 rows=10000 loops=1) + Buffers: shared hit=99720 + -> Index Scan using products_pkey on public.products (cost=0.29..387.29 rows=10000 width=16) (actual time=0.013..1.850 rows=10000 loops=1) Output: products.id, products.name, products.description, products.price, products.created_at Buffers: shared hit=143 - -> Index Scan using reviews_product_id_idx on public.reviews (cost=0.29..5699.91 rows=100000 width=8) (actual time=0.008..34.368 rows=100000 loops=1) + -> Index Scan using reviews_product_id_idx on public.reviews (cost=0.29..5700.28 rows=100000 width=8) (actual time=0.015..35.760 rows=100000 loops=1) Output: reviews.id, reviews.review_date, reviews.comment, reviews.product_id - Buffers: shared hit=99622 - -> Materialize (cost=0.42..50388.13 rows=1000000 width=8) (actual time=0.010..626.788 rows=9995969 loops=1) + Buffers: shared hit=99577 + -> Materialize (cost=0.42..50400.42 rows=1000000 width=8) (actual time=0.015..638.811 rows=9998150 loops=1) Output: orders.id, orders.order_date, orders.customer_name, orders.product_id - Buffers: shared hit=994158 - -> Index Scan using orders_product_id_idx on public.orders (cost=0.42..47888.13 rows=1000000 width=8) (actual time=0.009..324.399 rows=1000000 loops=1) + Buffers: shared hit=994165 + -> Index Scan using orders_product_id_idx on public.orders (cost=0.42..47900.42 rows=1000000 width=8) (actual time=0.013..325.213 rows=1000000 loops=1) Output: orders.id, orders.order_date, orders.customer_name, orders.product_id - Buffers: shared hit=994158 + Buffers: shared hit=994165 Planning: Buffers: shared hit=181 - Planning Time: 0.467 ms + Planning Time: 0.448 ms JIT: Functions: 15 Options: Inlining false, Optimization false, Expressions true, Deforming true - Timing: Generation 0.365 ms, Inlining 0.000 ms, Optimization 0.194 ms, Emission 4.497 ms, Total 5.055 ms - Execution Time: 2511.587 ms + Timing: Generation 0.370 ms, Inlining 0.000 ms, Optimization 0.199 ms, Emission 4.585 ms, Total 5.154 ms + Execution Time: 2696.039 ms (32 rows) -- cgit v1.3