diff options
Diffstat (limited to 'content/posts/postgres-aggregates/draft.md')
| -rw-r--r-- | content/posts/postgres-aggregates/draft.md | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/content/posts/postgres-aggregates/draft.md b/content/posts/postgres-aggregates/draft.md new file mode 100644 index 0000000..ab9cfd1 --- /dev/null +++ b/content/posts/postgres-aggregates/draft.md @@ -0,0 +1,68 @@ +## 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 |