explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iK4J

Settings
# exclusive inclusive rows x rows loops node
1. 10.752 21,590.262 ↓ 65.2 14,743 1

Sort (cost=727,722.11..727,722.68 rows=226 width=84) (actual time=21,589.712..21,590.262 rows=14,743 loops=1)

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

CTE cacategorytreecte

3. 0.461 18.172 ↑ 77.0 607 1

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

4. 0.588 8.479 ↓ 1.0 607 1

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

  • Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
5. 0.652 0.652 ↑ 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.005..0.652 rows=2,750 loops=1)

  • Filter: (cac_id IS NOT NULL)
6. 0.158 7.239 ↓ 1.0 607 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
7. 3.928 7.081 ↓ 1.0 607 1

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

  • Group Key: suppliers1__1.casc_cac_id
8. 3.153 3.153 ↓ 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.010..3.153 rows=15,047 loops=1)

9. 0.182 9.232 ↑ 10.2 451 1

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

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

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

11. 0.126 8.994 ↑ 1.0 456 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.166 8.868 ↑ 1.0 462 1

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

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

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

  • Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
14. 0.347 0.347 ↑ 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.005..0.347 rows=2,750 loops=1)

15. 0.113 6.574 ↓ 1.0 607 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
16. 3.831 6.461 ↓ 1.0 607 1

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

  • Group Key: suppliers2_.casc_cac_id
17. 2.630 2.630 ↓ 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.005..2.630 rows=15,047 loops=1)

18. 0.502 1.111 ↑ 1.1 2,539 1

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

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

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

20. 21.167 21,579.510 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.98..719,065.11 rows=226 width=84) (actual time=23.938..21,579.510 rows=14,743 loops=1)

21. 4.439 92.535 ↓ 65.2 14,743 1

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

22. 4.481 43.867 ↓ 65.2 14,743 1

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

23. 0.591 22.286 ↓ 8.8 380 1

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

24. 0.513 20.175 ↓ 1.9 380 1

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

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

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

  • Filter: (cac_cocam_id = 5363)
  • Rows Removed by Filter: 2355
26. 0.127 18.913 ↓ 3.0 607 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
27. 0.351 18.786 ↓ 3.0 607 1

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

  • Group Key: cacategory7_.id
28. 18.435 18.435 ↑ 77.0 607 1

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

29. 1.520 1.520 ↑ 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.004..0.004 rows=1 loops=380)

  • Index Cond: (casc_cac_id = cacategory0_.cac_id)
  • Heap Fetches: 3
30. 17.100 17.100 ↓ 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.002..0.045 rows=39 loops=380)

  • Index Cond: (cacategory0_.cac_id = casc_cac_id)
31. 44.229 44.229 ↑ 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.003..0.003 rows=1 loops=14,743)

  • Index Cond: (suppliers1_.casc_cas_id = cas_id)
32. 44.229 44.229 ↑ 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.003..0.003 rows=1 loops=14,743)

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

SubPlan (for Nested Loop Left Join)

34. 14.743 21,421.579 ↑ 1.0 1 14,743

Aggregate (cost=3,174.06..3,174.07 rows=1 width=32) (actual time=1.453..1.453 rows=1 loops=14,743)

35. 56.258 21,406.836 ↑ 13.0 2 14,743

Nested Loop (cost=223.78..3,173.99 rows=26 width=7) (actual time=1.256..1.452 rows=2 loops=14,743)

36. 16,585.875 21,318.378 ↑ 13.0 2 14,743

Bitmap Heap Scan on ca_aggregated_spend caaggregat4_ (cost=223.36..3,042.48 rows=26 width=4) (actual time=1.254..1.446 rows=2 loops=14,743)

  • 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))
  • Filter: (caas_cac_id = cacategory0_.cac_id)
  • Rows Removed by Filter: 8965
  • Heap Blocks: exact=7799047
37. 4,732.503 4,732.503 ↓ 1.0 8,967 14,743

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

  • 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))
38. 32.200 32.200 ↑ 1.0 1 32,200

Index Only Scan using idx_caasv_currency_filter_actual_spend on ca_aggregated_spend_value spendvalue5_ (cost=0.42..5.06 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=32,200)

  • Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text))
  • Heap Fetches: 21354
Planning time : 5.198 ms
Execution time : 21,590.944 ms