diff options
Diffstat (limited to 'content/posts')
| -rw-r--r-- | content/posts/postgres-aggregates/aggregation.txt | 32 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/index.md | 6 |
2 files changed, 17 insertions, 21 deletions
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) diff --git a/content/posts/postgres-aggregates/index.md b/content/posts/postgres-aggregates/index.md index d1664ee..9fb215e 100644 --- a/content/posts/postgres-aggregates/index.md +++ b/content/posts/postgres-aggregates/index.md @@ -48,7 +48,7 @@ We will see that there are several ways to write a query that outputs the same r We will then use a **visualization tool** to make it easier to read the plan. I use **Dalibo Explain**[^dalibo-explain], which is a free tool that can be used online. It is also open source and standalone, so you can download the HTML page to use it locally. -I will also use **hyperfine**[^hyperfine] to benchmark the different queries. You don't necessarily need it to test the queries at home. +I will also use **hyperfine**[^hyperfine] to benchmark the different queries. You don't necessarily need it to test the queries at home. Note that there will be a difference between the performances of the benchmark and the `EXPLAIN ANALYZE`, that's expected. ## 1. Aggregation @@ -107,8 +107,6 @@ Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f aggregation.sql On my computer, with the data provided in the introduction, I measured this query takes **1622ms** to execute. This seems too slow for a “web app” usage where you would need to query this data on demand for a user. -<!-- Reanalyse plan because of time diff (2.4 -> 1.6) --> - Using Dalibo, we can see which are the most costly parts of the query:  @@ -156,8 +154,6 @@ When we analyze the new query plan, we can see some major differences:  -<!-- TODO: Display loops in the plan --> - There are now sub-plans: because the subqueries reference a column of the outer query (`products.id`), these must be run for each row that is found in `products`. This is shown by the keywords `loops: 10000`. This is called a **correlated subquery**[^wiki-correlated-subquery]. These queries, even if numerous, are highly efficient because they can use the indexes we created to quickly find to corresponding rows: this is what the Bitmap Index Scan and Bitmap Heap Scan show. |