explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sL7F

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=16,703.43..16,703.43 rows=1 width=84) (actual rows= loops=)

  • Sort Key: cacategory0_.cac_code
2.          

CTE cacategorytreecte

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=328.24..7,354.16 rows=25,692 width=11) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Join (cost=328.24..396.31 rows=392 width=11) (actual rows= loops=)

  • Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
5. 0.000 0.000 ↓ 0.0

Seq Scan on ca_category cacategory0__1 (cost=0.00..57.94 rows=2,194 width=11) (actual rows= loops=)

  • Filter: (cac_id IS NOT NULL)
6. 0.000 0.000 ↓ 0.0

Hash (cost=323.34..323.34 rows=392 width=4) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=319.42..323.34 rows=392 width=4) (actual rows= loops=)

  • Group Key: suppliers1__1.casc_cac_id
8. 0.000 0.000 ↓ 0.0

Seq Scan on ca_supplier_category suppliers1__1 (cost=0.00..276.54 rows=17,154 width=4) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=491.71..644.40 rows=2,530 width=11) (actual rows= loops=)

  • Hash Cond: (cacategory1_.id = cacategory0__2.cac_parent_id)
10. 0.000 0.000 ↓ 0.0

WorkTable Scan on cacategorytreecte cacategory1_ (cost=0.00..78.40 rows=3,920 width=4) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=487.82..487.82 rows=311 width=15) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=413.61..487.82 rows=311 width=15) (actual rows= loops=)

  • Hash Cond: (cacategory0__2.cac_id = children3_.cac_parent_id)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=328.24..396.31 rows=392 width=15) (actual rows= loops=)

  • Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
14. 0.000 0.000 ↓ 0.0

Seq Scan on ca_category cacategory0__2 (cost=0.00..57.94 rows=2,194 width=15) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=323.34..323.34 rows=392 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=319.42..323.34 rows=392 width=4) (actual rows= loops=)

  • Group Key: suppliers2_.casc_cac_id
17. 0.000 0.000 ↓ 0.0

Seq Scan on ca_supplier_category suppliers2_ (cost=0.00..276.54 rows=17,154 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=57.94..57.94 rows=2,194 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on ca_category children3_ (cost=0.00..57.94 rows=2,194 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=583.70..9,349.26 rows=1 width=84) (actual rows= loops=)

  • Join Filter: (cacategory0_.cac_id = cacategory7_.id)
21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.63..159.85 rows=1 width=56) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.20..152.39 rows=1 width=35) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.92..152.03 rows=1 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.29..64.30 rows=1 width=15) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on ca_category cacategory0_ (cost=0.00..63.42 rows=1 width=11) (actual rows= loops=)

  • Filter: (cac_cocam_id = 5363)
26. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_ca_supplier_category on ca_supplier_category suppliers6_ (cost=0.29..9.06 rows=44 width=4) (actual rows= loops=)

  • Index Cond: (casc_cac_id = cacategory0_.cac_id)
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ca_supplier_category suppliers1_ (cost=4.63..87.29 rows=44 width=9) (actual rows= loops=)

  • Recheck Cond: (cacategory0_.cac_id = casc_cac_id)
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on pk_ca_supplier_category (cost=0.00..4.62 rows=44 width=0) (actual rows= loops=)

  • Index Cond: (cacategory0_.cac_id = casc_cac_id)
29. 0.000 0.000 ↓ 0.0

Index Scan using pk_ca_supplier on ca_supplier casupplier2_ (cost=0.29..0.36 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (suppliers1_.casc_cas_id = cas_id)
30. 0.000 0.000 ↓ 0.0

Index Scan using pk_party on party party3_ (cost=0.43..7.46 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (casupplier2_.cas_p_id = p_id)
31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=578.07..580.07 rows=200 width=4) (actual rows= loops=)

  • Group Key: cacategory7_.id
32. 0.000 0.000 ↓ 0.0

CTE Scan on cacategorytreecte cacategory7_ (cost=0.00..513.84 rows=25,692 width=4) (actual rows= loops=)

33.          

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,606.82..8,606.83 rows=1 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8,606.44 rows=154 width=7) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on ca_aggregated_spend caaggregat4_ (cost=0.00..7,911.69 rows=150 width=4) (actual rows= loops=)

  • 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))
37. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_caasv_currency_filter_actual_spend on ca_aggregated_spend_value spendvalue5_ (cost=0.42..4.63 rows=1 width=11) (actual rows= loops=)

  • Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text))