1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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
|
