explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FPA5

Settings
# exclusive inclusive rows x rows loops node
1. 22.191 204,023.874 ↓ 65.2 14,743 1

Sort (cost=1,204,035.98..1,204,036.55 rows=226 width=84) (actual time=204,023.396..204,023.874 rows=14,743 loops=1)

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

CTE cacategorytreecte

3. 0.498 18.189 ↑ 77.0 607 1

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

4. 0.578 8.286 ↓ 1.0 607 1

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

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

  • Filter: (cac_id IS NOT NULL)
6. 0.136 7.023 ↓ 1.0 607 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
7. 3.793 6.887 ↓ 1.0 607 1

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

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

9. 0.208 9.405 ↑ 10.2 451 1

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

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

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

11. 0.136 9.142 ↑ 1.0 456 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.186 9.006 ↑ 1.0 462 1

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

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

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

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

15. 0.129 6.689 ↓ 1.0 607 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
16. 3.841 6.560 ↓ 1.0 607 1

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

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

18. 0.578 1.180 ↑ 1.1 2,539 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 122kB
19. 0.602 0.602 ↑ 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.602 rows=2,750 loops=1)

20. 49.536 204,001.683 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.98..1,195,378.98 rows=226 width=84) (actual time=43.291..204,001.683 rows=14,743 loops=1)

21. 29.103 144.915 ↓ 65.2 14,743 1

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

22. 8.242 56.840 ↓ 65.2 14,743 1

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

23. 0.765 22.378 ↓ 8.8 380 1

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

24. 0.653 20.093 ↓ 1.9 380 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
27. 0.404 18.855 ↓ 3.0 607 1

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

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

CTE Scan on cacategorytreecte cacategory7_ (cost=0.00..934.40 rows=46,720 width=4) (actual time=7.060..18.451 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. 26.220 26.220 ↓ 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.069 rows=39 loops=380)

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

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

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

SubPlan (for Nested Loop Left Join)

34. 44.229 203,733.517 ↑ 1.0 1 14,743

Aggregate (cost=5,281.64..5,281.65 rows=1 width=32) (actual time=13.819..13.819 rows=1 loops=14,743)

35. 53.544 203,689.288 ↑ 13.5 2 14,743

Nested Loop (cost=0.42..5,281.57 rows=27 width=7) (actual time=13.269..13.816 rows=2 loops=14,743)

36. 203,571.344 203,571.344 ↑ 13.5 2 14,743

Seq Scan on ca_aggregated_spend caaggregat4_ (cost=0.00..5,141.62 rows=27 width=4) (actual time=13.265..13.808 rows=2 loops=14,743)

  • Filter: ((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) AND (caas_cac_id = cacategory0_.cac_id))
  • Rows Removed by Filter: 141431
37. 64.400 64.400 ↑ 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.18 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=32,200)

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