explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s43o

Settings
# exclusive inclusive rows x rows loops node
1. 5.069 173.174 ↓ 65.2 14,743 1

Sort (cost=22,392.38..22,392.95 rows=226 width=84) (actual time=172.703..173.174 rows=14,743 loops=1)

  • Sort Key: cacategory0_.cac_code
  • Sort Method: quicksort Memory: 2008kB
2.          

CTE cacategorytreecte

3. 0.400 16.545 ↑ 77.0 607 1

Recursive Union (cost=284.29..8,648.16 rows=46,720 width=11) (actual time=6.315..16.545 rows=607 loops=1)

4. 0.512 7.329 ↓ 1.0 607 1

Hash Join (cost=284.29..363.68 rows=600 width=11) (actual time=6.313..7.329 rows=607 loops=1)

  • Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
5. 0.526 0.526 ↑ 1.0 2,750 1

Seq Scan on ca_category cacategory0__1 (cost=0.00..65.50 rows=2,750 width=11) (actual time=0.006..0.526 rows=2,750 loops=1)

  • Filter: (cac_id IS NOT NULL)
6. 0.121 6.291 ↓ 1.0 607 1

Hash (cost=276.79..276.79 rows=600 width=4) (actual time=6.291..6.291 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
7. 3.605 6.170 ↓ 1.0 607 1

HashAggregate (cost=270.79..276.79 rows=600 width=4) (actual time=6.052..6.170 rows=607 loops=1)

  • Group Key: suppliers1__1.casc_cac_id
8. 2.565 2.565 ↓ 1.0 15,047 1

Seq Scan on ca_supplier_category suppliers1__1 (cost=0.00..234.83 rows=14,383 width=4) (actual time=0.005..2.565 rows=15,047 loops=1)

9. 0.183 8.816 ↑ 10.2 451 1

Hash Join (cost=478.93..735.01 rows=4,612 width=11) (actual time=8.586..8.816 rows=451 loops=1)

  • Hash Cond: (cacategory1_.id = cacategory0__2.cac_parent_id)
10. 0.057 0.057 ↑ 9.9 607 1

WorkTable Scan on cacategorytreecte cacategory1_ (cost=0.00..120.00 rows=6,000 width=4) (actual time=0.002..0.057 rows=607 loops=1)

11. 0.111 8.576 ↑ 1.0 456 1

Hash (cost=472.99..472.99 rows=475 width=15) (actual time=8.576..8.576 rows=456 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.161 8.465 ↑ 1.0 462 1

Hash Anti Join (cost=384.16..472.99 rows=475 width=15) (actual time=7.330..8.465 rows=462 loops=1)

  • Hash Cond: (cacategory0__2.cac_id = children3_.cac_parent_id)
13. 0.638 7.253 ↓ 1.0 607 1

Hash Join (cost=284.29..363.68 rows=600 width=15) (actual time=6.272..7.253 rows=607 loops=1)

  • Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
14. 0.363 0.363 ↑ 1.0 2,750 1

Seq Scan on ca_category cacategory0__2 (cost=0.00..65.50 rows=2,750 width=15) (actual time=0.004..0.363 rows=2,750 loops=1)

15. 0.110 6.252 ↓ 1.0 607 1

Hash (cost=276.79..276.79 rows=600 width=4) (actual time=6.252..6.252 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
16. 3.642 6.142 ↓ 1.0 607 1

HashAggregate (cost=270.79..276.79 rows=600 width=4) (actual time=6.019..6.142 rows=607 loops=1)

  • Group Key: suppliers2_.casc_cac_id
17. 2.500 2.500 ↓ 1.0 15,047 1

Seq Scan on ca_supplier_category suppliers2_ (cost=0.00..234.83 rows=14,383 width=4) (actual time=0.004..2.500 rows=15,047 loops=1)

18. 0.478 1.051 ↑ 1.1 2,539 1

Hash (cost=65.50..65.50 rows=2,750 width=4) (actual time=1.051..1.051 rows=2,539 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 122kB
19. 0.573 0.573 ↑ 1.0 2,750 1

Seq Scan on ca_category children3_ (cost=0.00..65.50 rows=2,750 width=4) (actual time=0.004..0.573 rows=2,750 loops=1)

20.          

CTE test

21. 0.000 93.606 ↑ 84.8 4 1

Finalize GroupAggregate (cost=10,239.74..10,285.50 rows=339 width=36) (actual time=93.603..93.606 rows=4 loops=1)

  • Group Key: caaggregat4_.caas_cac_id
22. 0.000 93.744 ↑ 56.5 6 1

Gather Merge (cost=10,239.74..10,278.72 rows=339 width=36) (actual time=93.596..93.744 rows=6 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
23. 0.028 179.600 ↑ 113.0 3 2

Sort (cost=9,239.73..9,240.57 rows=339 width=36) (actual time=89.799..89.800 rows=3 loops=2)

  • Sort Key: caaggregat4_.caas_cac_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
24. 1.442 179.572 ↑ 113.0 3 2

Partial HashAggregate (cost=9,221.24..9,225.48 rows=339 width=36) (actual time=89.784..89.786 rows=3 loops=2)

  • Group Key: caaggregat4_.caas_cac_id
25. 21.632 178.130 ↑ 1.1 4,484 2

Parallel Hash Join (cost=3,033.15..9,196.01 rows=5,046 width=11) (actual time=73.801..89.065 rows=4,484 loops=2)

  • Hash Cond: (spendvalue5_.caasv_caas_id = caaggregat4_.caas_id)
26. 149.266 149.266 ↑ 1.2 70,698 2

Parallel Seq Scan on ca_aggregated_spend_value spendvalue5_ (cost=0.00..5,947.35 rows=82,097 width=11) (actual time=4.604..74.633 rows=70,698 loops=2)

  • Filter: ((caasv_cur)::text = 'EUR'::text)
  • Rows Removed by Filter: 134434
27. 2.160 7.232 ↑ 1.1 4,484 2

Parallel Hash (cost=2,969.23..2,969.23 rows=5,114 width=8) (actual time=3.616..3.616 rows=4,484 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 512kB
28. 4.183 5.072 ↑ 1.1 4,484 2

Parallel Bitmap Heap Scan on ca_aggregated_spend caaggregat4_ (cost=225.52..2,969.23 rows=5,114 width=8) (actual time=0.539..2.536 rows=4,484 loops=2)

  • Recheck Cond: ((caas_period >= '2018-11-29 00:00:00'::timestamp without time zone) AND (caas_period <= '2019-11-29 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=509
29. 0.889 0.889 ↓ 1.0 8,967 1

Bitmap Index Scan on idx_caas_period_filter (cost=0.00..223.35 rows=8,693 width=0) (actual time=0.889..0.889 rows=8,967 loops=1)

  • Index Cond: ((caas_period >= '2018-11-29 00:00:00'::timestamp without time zone) AND (caas_period <= '2019-11-29 00:00:00'::timestamp without time zone))
30. 2.711 168.105 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.98..3,449.88 rows=226 width=84) (actual time=111.208..168.105 rows=14,743 loops=1)

31. 7.850 47.450 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.56..1,387.87 rows=226 width=31) (actual time=17.584..47.450 rows=14,743 loops=1)

32. 1.448 24.857 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.27..1,296.56 rows=226 width=20) (actual time=17.575..24.857 rows=14,743 loops=1)

33. 0.214 18.469 ↓ 8.8 380 1

Nested Loop Semi Join (cost=1,055.99..1,197.14 rows=43 width=11) (actual time=17.567..18.469 rows=380 loops=1)

34. 0.115 17.875 ↓ 1.9 380 1

Hash Join (cost=1,055.70..1,131.31 rows=198 width=15) (actual time=17.533..17.875 rows=380 loops=1)

  • Hash Cond: (cacategory0_.cac_id = cacategory7_.id)
35. 0.542 0.542 ↑ 1.0 395 1

Seq Scan on ca_category cacategory0_ (cost=0.00..72.38 rows=395 width=11) (actual time=0.306..0.542 rows=395 loops=1)

  • Filter: (cac_cocam_id = 5363)
  • Rows Removed by Filter: 2355
36. 0.113 17.218 ↓ 3.0 607 1

Hash (cost=1,053.20..1,053.20 rows=200 width=4) (actual time=17.218..17.218 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
37. 0.337 17.105 ↓ 3.0 607 1

HashAggregate (cost=1,051.20..1,053.20 rows=200 width=4) (actual time=16.969..17.105 rows=607 loops=1)

  • Group Key: cacategory7_.id
38. 16.768 16.768 ↑ 77.0 607 1

CTE Scan on cacategorytreecte cacategory7_ (cost=0.00..934.40 rows=46,720 width=4) (actual time=6.317..16.768 rows=607 loops=1)

39. 0.380 0.380 ↑ 24.0 1 380

Index Only Scan using pk_ca_supplier_category on ca_supplier_category suppliers6_ (cost=0.29..1.15 rows=24 width=4) (actual time=0.001..0.001 rows=1 loops=380)

  • Index Cond: (casc_cac_id = cacategory0_.cac_id)
  • Heap Fetches: 3
40. 4.940 4.940 ↓ 1.6 39 380

Index Scan using pk_ca_supplier_category on ca_supplier_category suppliers1_ (cost=0.29..2.07 rows=24 width=9) (actual time=0.001..0.013 rows=39 loops=380)

  • Index Cond: (cacategory0_.cac_id = casc_cac_id)
41. 14.743 14.743 ↑ 1.0 1 14,743

Index Scan using pk_ca_supplier on ca_supplier casupplier2_ (cost=0.29..0.40 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=14,743)

  • Index Cond: (suppliers1_.casc_cas_id = cas_id)
42. 14.743 14.743 ↑ 1.0 1 14,743

Index Scan using pk_party on party party3_ (cost=0.42..1.49 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=14,743)

  • Index Cond: (casupplier2_.cas_p_id = p_id)
43.          

SubPlan (for Nested Loop Left Join)

44. 103.201 103.201 ↓ 0.0 0 14,743

CTE Scan on test t (cost=0.00..7.63 rows=2 width=32) (actual time=0.007..0.007 rows=0 loops=14,743)

  • Filter: (id = cacategory0_.cac_id)
  • Rows Removed by Filter: 4
Planning time : 3.111 ms
Execution time : 173.990 ms