diff options
Diffstat (limited to 'content/posts/postgres-aggregates')
| -rw-r--r-- | content/posts/postgres-aggregates/draft.md | 68 | ||||
| -rw-r--r-- | content/posts/postgres-aggregates/index.md | 4 |
2 files changed, 2 insertions, 70 deletions
diff --git a/content/posts/postgres-aggregates/draft.md b/content/posts/postgres-aggregates/draft.md deleted file mode 100644 index ab9cfd1..0000000 --- a/content/posts/postgres-aggregates/draft.md +++ /dev/null @@ -1,68 +0,0 @@ -## 4. Sequential CTEs - -```sql -WITH product_with_reviews AS ( - SELECT - products.id as product_id, - products.name as product_name, - count(reviews.id) AS num_reviews - FROM - products - LEFT JOIN reviews ON reviews.product_id = products.id - GROUP BY - products.id, - products.name -) -SELECT - product_with_reviews.product_id, - product_name, - num_reviews, - count(id) AS num_orders -FROM - product_with_reviews - LEFT JOIN orders ON orders.product_id = product_with_reviews.product_id -GROUP BY - product_with_reviews.product_id, - product_name, - num_reviews; -``` - -``` -Benchmark 1: psql postgres://postgres@localhost:5432/postgres -f sequential-ctes.sql - Time (mean ± σ): 360.9 ms ± 4.5 ms [User: 15.5 ms, System: 4.2 ms] - Range (min … max): 355.0 ms … 369.2 ms 10 runs -``` - -**This query takes 361ms.** - -It’s longer than the merged CTEs, basically because the hash join happens before aggregation. So we have much more data to process at the end of the pipeline. - -<!-- TODO: Replace by screenshot --> - - - -## 5. Going further - -| Query | Time 100% | Time 10% | -| -------------- | --------- | -------- | -| Aggregation | 2450 | 270 | -| Merged CTE | 90 | 80 | -| Subquery | 650 | 60 | -| Sequential CTE | 361 | 85 | - -- What happens if we filter out 10% ? 90% of products base of products table? - - Sequential CTE would be faster, adding the filter in the beginning - - Aggregation & Subquery too would be faster - - Merged CTE would still do all the work, or almost (less data to process at some point) -- We can see plans that scale almost linearly : Aggregation & Subquery -- Sequential subquery does not scale as well, I don’t know why -- But Merged CTE only scales down for a small fraction, because most of the work is done without knowing what products will be selected. -- Maybe a conclusion is that there is no silver bullet: we can make queries have a small cost when done a a large queryset, compared to others, but when the queryset is much smaller, the gain will be less important than others. -- The same way as finding 1 largest element is a seq scan O(n), but finding n largest elements out of n should use a sort O(nlogn). Finding the sweet spot where to swap your algorithm is finding k such as kn ~ nlogn ⇒ k = log n. Ex: for 10k elements, if you need to find more than 10, use sort. - -## TODOs and open questions - -- Are there differences using different postgres versions? -- Add charts to visualize -- Traffic analytics? -- Short intro to postgres engine and query plans diff --git a/content/posts/postgres-aggregates/index.md b/content/posts/postgres-aggregates/index.md index 61b4591..17f53cf 100644 --- a/content/posts/postgres-aggregates/index.md +++ b/content/posts/postgres-aggregates/index.md @@ -1,7 +1,7 @@ --- title: "Efficiente PostgreSQL Aggregations" -draft: true -date: +draft: false +date: 2023-09-25 description: "Diving into PostgreSQL query plans to improve our tooling" categories: - PostgreSQL |