explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SYKw

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 0.322 ↓ 0.0 0 1

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

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

CTE cacategorytreecte

3. 0.000 0.000 ↓ 0.0 0

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

4. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=328.24..396.31 rows=392 width=11) (never executed)

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

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

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

Hash (cost=323.34..323.34 rows=392 width=4) (never executed)

7. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=319.42..323.34 rows=392 width=4) (never executed)

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

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

9. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=491.71..644.40 rows=2,530 width=11) (never executed)

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

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

11. 0.000 0.000 ↓ 0.0 0

Hash (cost=487.82..487.82 rows=311 width=15) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Hash Anti Join (cost=413.61..487.82 rows=311 width=15) (never executed)

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

Hash Join (cost=328.24..396.31 rows=392 width=15) (never executed)

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

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

15. 0.000 0.000 ↓ 0.0 0

Hash (cost=323.34..323.34 rows=392 width=4) (never executed)

16. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=319.42..323.34 rows=392 width=4) (never executed)

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

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

18. 0.000 0.000 ↓ 0.0 0

Hash (cost=57.94..57.94 rows=2,194 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

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

20. 0.001 0.298 ↓ 0.0 0 1

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

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

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

22. 0.000 0.297 ↓ 0.0 0 1

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

23. 0.000 0.297 ↓ 0.0 0 1

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

24. 0.000 0.297 ↓ 0.0 0 1

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

25. 0.297 0.297 ↓ 0.0 0 1

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

  • Filter: (cac_cocam_id = 5363)
  • Rows Removed by Filter: 2200
26. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

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

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

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

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

HashAggregate (cost=578.07..580.07 rows=200 width=4) (never executed)

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

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

33.          

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8,606.82..8,606.83 rows=1 width=32) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..8,606.44 rows=154 width=7) (never executed)

36. 0.000 0.000 ↓ 0.0 0

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

  • 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 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) (never executed)

  • Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text))
  • Heap Fetches: 0
Planning time : 0.773 ms
Execution time : 0.620 ms