explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 33de

Settings
# exclusive inclusive rows x rows loops node
1. 89.705 6,354.720 ↑ 25.0 400 1

Merge Left Join (cost=275,956.03..321,059.03 rows=10,000 width=48) (actual time=4,737.495..6,354.720 rows=400 loops=1)

  • Merge Cond: ((top_20_customer.top_20_c_id = c.customer_id) AND (top_20_category.top_20_ct_id = ct.category_id))
2.          

CTE top_20_category

3. 0.060 629.648 ↑ 1.0 20 1

Limit (cost=18,008.93..18,008.98 rows=20 width=36) (actual time=629.586..629.648 rows=20 loops=1)

4. 0.167 629.588 ↑ 5.0 20 1

Sort (cost=18,008.93..18,009.18 rows=100 width=36) (actual time=629.586..629.588 rows=20 loops=1)

  • Sort Key: (sum(((s_1.quantity)::numeric * s_1.price))) DESC
  • Sort Method: top-N heapsort Memory: 26kB
5. 0.767 629.421 ↑ 1.0 100 1

Finalize GroupAggregate (cost=17,980.18..18,006.27 rows=100 width=36) (actual time=628.354..629.421 rows=100 loops=1)

  • Group Key: c_1.category_id
6. 0.000 628.654 ↓ 1.5 300 1

Gather Merge (cost=17,980.18..18,003.52 rows=200 width=36) (actual time=628.342..628.654 rows=300 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.375 1,687.386 ↑ 1.0 100 3

Sort (cost=16,980.16..16,980.41 rows=100 width=36) (actual time=562.452..562.462 rows=100 loops=3)

  • Sort Key: c_1.category_id
  • Sort Method: quicksort Memory: 32kB
  • Worker 0: Sort Method: quicksort Memory: 32kB
  • Worker 1: Sort Method: quicksort Memory: 32kB
8. 681.282 1,687.011 ↑ 1.0 100 3

Partial HashAggregate (cost=16,975.59..16,976.84 rows=100 width=36) (actual time=562.281..562.337 rows=100 loops=3)

  • Group Key: c_1.category_id
9. 288.720 1,005.729 ↑ 1.3 333,333 3

Hash Join (cost=33.75..12,808.92 rows=416,667 width=13) (actual time=1.623..335.243 rows=333,333 loops=3)

  • Hash Cond: (p_1.category_id = c_1.category_id)
10. 396.888 715.851 ↑ 1.3 333,333 3

Hash Join (cost=30.50..11,665.56 rows=416,667 width=13) (actual time=1.210..238.617 rows=333,333 loops=3)

  • Hash Cond: (s_1.product_id = p_1.product_id)
11. 316.437 316.437 ↑ 1.3 333,333 3

Parallel Seq Scan on sale s_1 (cost=0.00..10,536.67 rows=416,667 width=13) (actual time=0.332..105.479 rows=333,333 loops=3)

12. 0.882 2.526 ↑ 1.0 1,000 3

Hash (cost=18.00..18.00 rows=1,000 width=8) (actual time=0.842..0.842 rows=1,000 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
13. 1.644 1.644 ↑ 1.0 1,000 3

Seq Scan on product p_1 (cost=0.00..18.00 rows=1,000 width=8) (actual time=0.252..0.548 rows=1,000 loops=3)

14. 0.159 1.158 ↑ 1.0 100 3

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.386..0.386 rows=100 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
15. 0.999 0.999 ↑ 1.0 100 3

Seq Scan on category c_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.312..0.333 rows=100 loops=3)

16.          

CTE top_20_customer

17. 27.210 1,559.617 ↑ 1.0 20 1

Limit (cost=97,925.75..97,925.80 rows=20 width=36) (actual time=1,532.407..1,559.617 rows=20 loops=1)

18. 25.135 1,532.407 ↑ 5,000.0 20 1

Sort (cost=97,925.75..98,175.75 rows=100,000 width=36) (actual time=1,532.406..1,532.407 rows=20 loops=1)

  • Sort Key: (sum(((s_2.quantity)::numeric * s_2.price))) DESC
  • Sort Method: top-N heapsort Memory: 26kB
19. 214.405 1,507.272 ↑ 1.0 99,995 1

Finalize GroupAggregate (cost=62,971.49..95,264.79 rows=100,000 width=36) (actual time=889.397..1,507.272 rows=99,995 loops=1)

  • Group Key: c_2.customer_id
20. 0.000 1,292.867 ↓ 1.4 289,224 1

Gather Merge (cost=62,971.49..92,514.79 rows=200,000 width=36) (actual time=889.379..1,292.867 rows=289,224 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 775.335 3,413.010 ↑ 1.0 96,408 3

Partial GroupAggregate (cost=61,971.46..68,429.80 rows=100,000 width=36) (actual time=807.629..1,137.670 rows=96,408 loops=3)

  • Group Key: c_2.customer_id
22. 1,109.331 2,637.675 ↑ 1.3 333,333 3

Sort (cost=61,971.46..63,013.13 rows=416,667 width=13) (actual time=807.597..879.225 rows=333,333 loops=3)

  • Sort Key: c_2.customer_id
  • Sort Method: external merge Disk: 7600kB
  • Worker 0: Sort Method: external merge Disk: 7416kB
  • Worker 1: Sort Method: external merge Disk: 7600kB
23. 375.042 1,528.344 ↑ 1.3 333,333 3

Hash Join (cost=3,227.30..15,956.15 rows=416,667 width=13) (actual time=33.696..509.448 rows=333,333 loops=3)

  • Hash Cond: (s_2.product_id = p_2.product_id)
24. 680.328 1,150.776 ↑ 1.3 333,333 3

Parallel Hash Join (cost=3,196.80..14,827.26 rows=416,667 width=17) (actual time=32.812..383.592 rows=333,333 loops=3)

  • Hash Cond: (s_2.customer_id = c_2.customer_id)
25. 375.459 375.459 ↑ 1.3 333,333 3

Parallel Seq Scan on sale s_2 (cost=0.00..10,536.67 rows=416,667 width=17) (actual time=0.336..125.153 rows=333,333 loops=3)

26. 40.530 94.989 ↑ 1.3 33,333 3

Parallel Hash (cost=2,675.96..2,675.96 rows=41,667 width=4) (actual time=31.663..31.663 rows=33,333 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4960kB
27. 54.459 54.459 ↓ 2.4 100,000 1

Parallel Index Only Scan using customer_pkey on customer c_2 (cost=0.29..2,675.96 rows=41,667 width=4) (actual time=0.352..54.459 rows=100,000 loops=1)

  • Heap Fetches: 100000
28. 0.846 2.526 ↑ 1.0 1,000 3

Hash (cost=18.00..18.00 rows=1,000 width=4) (actual time=0.842..0.842 rows=1,000 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
29. 1.680 1.680 ↑ 1.0 1,000 3

Seq Scan on product p_2 (cost=0.00..18.00 rows=1,000 width=4) (actual time=0.305..0.560 rows=1,000 loops=3)

30. 0.198 2,189.771 ↑ 1.0 400 1

Sort (cost=48.18..49.18 rows=400 width=8) (actual time=2,189.720..2,189.771 rows=400 loops=1)

  • Sort Key: top_20_customer.top_20_c_id, top_20_category.top_20_ct_id
  • Sort Method: quicksort Memory: 43kB
31. 0.179 2,189.573 ↑ 1.0 400 1

Sort (cost=25.89..26.89 rows=400 width=8) (actual time=2,189.552..2,189.573 rows=400 loops=1)

  • Sort Key: top_20_category.top_20_ct_id, top_20_customer.top_20_c_id
  • Sort Method: quicksort Memory: 43kB
32. 0.052 2,189.394 ↑ 1.0 400 1

Nested Loop (cost=0.00..8.60 rows=400 width=8) (actual time=2,162.007..2,189.394 rows=400 loops=1)

33. 629.682 629.682 ↑ 1.0 20 1

CTE Scan on top_20_category (cost=0.00..0.40 rows=20 width=4) (actual time=629.595..629.682 rows=20 loops=1)

34. 1,559.660 1,559.660 ↑ 1.0 20 20

CTE Scan on top_20_customer (cost=0.00..0.40 rows=20 width=4) (actual time=76.621..77.983 rows=20 loops=20)

35. 1,205.666 4,075.244 ↑ 1.1 919,384 1

GroupAggregate (cost=159,973.08..189,973.08 rows=1,000,000 width=48) (actual time=2,515.415..4,075.244 rows=919,384 loops=1)

  • Group Key: c.customer_id, ct.category_id
36. 1,323.253 2,869.578 ↑ 1.0 970,197 1

Sort (cost=159,973.08..162,473.08 rows=1,000,000 width=17) (actual time=2,515.401..2,869.578 rows=970,197 loops=1)

  • Sort Key: c.customer_id, ct.category_id
  • Sort Method: external merge Disk: 26464kB
37. 202.915 1,546.325 ↑ 1.0 1,000,000 1

Hash Join (cost=3,326.75..39,805.24 rows=1,000,000 width=17) (actual time=39.438..1,546.325 rows=1,000,000 loops=1)

  • Hash Cond: (p.category_id = ct.category_id)
38. 243.808 1,343.345 ↑ 1.0 1,000,000 1

Hash Join (cost=3,323.50..37,065.74 rows=1,000,000 width=17) (actual time=39.362..1,343.345 rows=1,000,000 loops=1)

  • Hash Cond: (s.product_id = p.product_id)
39. 769.228 1,099.286 ↑ 1.0 1,000,000 1

Hash Join (cost=3,293.00..34,399.11 rows=1,000,000 width=17) (actual time=39.105..1,099.286 rows=1,000,000 loops=1)

  • Hash Cond: (s.customer_id = c.customer_id)
40. 291.611 291.611 ↑ 1.0 1,000,000 1

Seq Scan on sale s (cost=0.00..16,370.00 rows=1,000,000 width=17) (actual time=0.103..291.611 rows=1,000,000 loops=1)

41. 28.920 38.447 ↑ 1.0 100,000 1

Hash (cost=1,652.00..1,652.00 rows=100,000 width=4) (actual time=38.447..38.447 rows=100,000 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2781kB
42. 9.527 9.527 ↑ 1.0 100,000 1

Seq Scan on customer c (cost=0.00..1,652.00 rows=100,000 width=4) (actual time=0.023..9.527 rows=100,000 loops=1)

43. 0.126 0.251 ↑ 1.0 1,000 1

Hash (cost=18.00..18.00 rows=1,000 width=8) (actual time=0.251..0.251 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
44. 0.125 0.125 ↑ 1.0 1,000 1

Seq Scan on product p (cost=0.00..18.00 rows=1,000 width=8) (actual time=0.015..0.125 rows=1,000 loops=1)

45. 0.024 0.065 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.065..0.065 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
46. 0.041 0.041 ↑ 1.0 100 1

Seq Scan on category ct (cost=0.00..2.00 rows=100 width=4) (actual time=0.033..0.041 rows=100 loops=1)