explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zqob

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

CTE Scan on suggs_at_geo (cost=257.28..257.40 rows=1 width=708) (actual time=0.045..0.045 rows=0 loops=1)

2.          

CTE filtered_leaf_geos

3. 0.001 0.033 ↓ 0.0 0 1

Unique (cost=232.04..232.05 rows=1 width=150) (actual time=0.033..0.033 rows=0 loops=1)

4. 0.012 0.032 ↓ 0.0 0 1

Sort (cost=232.04..232.05 rows=1 width=150) (actual time=0.032..0.032 rows=0 loops=1)

  • Sort Key: geo_closure_leaf.descendant, (COALESCE(geography_closure_cte.id, '0'::character varying))
  • Sort Method: quicksort Memory: 25kB
5. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=214.56..232.03 rows=1 width=150) (actual time=0.020..0.020 rows=0 loops=1)

6. 0.007 0.019 ↓ 0.0 0 1

Hash Join (cost=214.43..230.68 rows=5 width=150) (actual time=0.019..0.019 rows=0 loops=1)

  • Hash Cond: (((COALESCE(geography_closure_cte.parent_id, '0'::character varying)))::text = (geo_closure_leaf.descendant)::text)
7. 0.000 0.000 ↓ 0.0 0

CTE Scan on geography_closure_cte (cost=202.40..212.00 rows=480 width=580) (never executed)

8.          

CTE geography_closure_cte

9. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=0.00..202.40 rows=480 width=1,548) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on geography_hierarchy (cost=0.00..10.30 rows=30 width=1,548) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=10.68..18.25 rows=45 width=1,548) (never executed)

  • Hash Cond: ((cte.parent_id)::text = (gh.parent_id)::text)
12. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on geography_closure_cte cte (cost=0.00..6.00 rows=300 width=1,032) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.30..10.30 rows=30 width=1,548) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on geography_hierarchy gh (cost=0.00..10.30 rows=30 width=1,548) (never executed)

15. 0.001 0.012 ↓ 0.0 0 1

Hash (cost=12.00..12.00 rows=2 width=118) (actual time=0.012..0.012 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on geo_closure_leaf (cost=0.00..12.00 rows=2 width=118) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: (('-1'::text = ANY ('{-1}'::text[])) OR ((ancestor)::text = ANY ('{-1}'::text[])))
17. 0.000 0.000 ↓ 0.0 0

Index Scan using geography_hierarchy_pk on geography_hierarchy parent_info (cost=0.14..0.25 rows=1 width=516) (never executed)

  • Index Cond: ((id)::text = ((COALESCE(geography_closure_cte.id, '0'::character varying)))::text)
  • Filter: ((geography_level_id)::text = '4'::text)
18.          

CTE curr

19. 0.001 0.043 ↓ 0.0 0 1

GroupAggregate (cost=8.21..8.25 rows=1 width=64) (actual time=0.043..0.043 rows=0 loops=1)

  • Group Key: filtered_leaf_geos.parent_id
20. 0.008 0.042 ↓ 0.0 0 1

Sort (cost=8.21..8.22 rows=1 width=166) (actual time=0.042..0.042 rows=0 loops=1)

  • Sort Key: filtered_leaf_geos.parent_id
  • Sort Method: quicksort Memory: 25kB
21. 0.001 0.034 ↓ 0.0 0 1

Nested Loop (cost=0.14..8.20 rows=1 width=166) (actual time=0.034..0.034 rows=0 loops=1)

  • Join Filter: ((filtered_leaf_geos.leaf_geo_id)::text = (rep_feedback_summary.geo_id)::text)
22. 0.033 0.033 ↓ 0.0 0 1

CTE Scan on filtered_leaf_geos (cost=0.00..0.02 rows=1 width=150) (actual time=0.033..0.033 rows=0 loops=1)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_rep_feedback_summary on rep_feedback_summary (cost=0.14..8.17 rows=1 width=352) (never executed)

  • Index Cond: ((business_unit_sk)::text = '1'::text)
  • Filter: ((('-1'::text = ANY ('{-1}'::text[])) OR ((salesforce_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((prod_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((metric_group_type)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((business_category_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((metric_group_sk)::text = ANY ('{-1}'::text[]))) AND (sugg_posted_month_recency >= 1) AND (sugg_posted_month_recency <= 3))
24.          

CTE prev

25. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=8.21..8.25 rows=1 width=64) (never executed)

  • Group Key: b.parent_id
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=8.21..8.22 rows=1 width=166) (never executed)

  • Sort Key: b.parent_id
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.14..8.20 rows=1 width=166) (never executed)

  • Join Filter: ((b.leaf_geo_id)::text = (a.geo_id)::text)
28. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_leaf_geos b (cost=0.00..0.02 rows=1 width=150) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_rep_feedback_summary on rep_feedback_summary a (cost=0.14..8.17 rows=1 width=352) (never executed)

  • Index Cond: ((business_unit_sk)::text = '1'::text)
  • Filter: ((('-1'::text = ANY ('{-1}'::text[])) OR ((salesforce_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((prod_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((metric_group_type)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((business_category_sk)::text = ANY ('{-1}'::text[]))) AND (('-1'::text = ANY ('{-1}'::text[])) OR ((metric_group_sk)::text = ANY ('{-1}'::text[]))) AND (sugg_posted_month_recency >= 4) AND (sugg_posted_month_recency <= 6))
30.          

CTE suggs_at_geo

31. 0.000 0.044 ↓ 0.0 0 1

Nested Loop (cost=0.14..8.72 rows=1 width=612) (actual time=0.044..0.044 rows=0 loops=1)

32. 0.001 0.044 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=96) (actual time=0.044..0.044 rows=0 loops=1)

  • Join Filter: ((curr.parent_id)::text = (prev.parent_id)::text)
33. 0.043 0.043 ↓ 0.0 0 1

CTE Scan on curr (cost=0.00..0.02 rows=1 width=64) (actual time=0.043..0.043 rows=0 loops=1)

34. 0.000 0.000 ↓ 0.0 0

CTE Scan on prev (cost=0.00..0.02 rows=1 width=64) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using geography_hierarchy_pk on geography_hierarchy b_1 (cost=0.14..8.15 rows=1 width=1,032) (never executed)

  • Index Cond: ((id)::text = (curr.parent_id)::text)