explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mAaF

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

CTE Scan on suggs_at_geo (cost=57.71..57.83 rows=1 width=708) (actual time=0.089..0.089 rows=0 loops=1)

2.          

CTE filtered_leaf_geos

3. 0.002 0.057 ↓ 0.0 0 1

Unique (cost=32.47..32.48 rows=1 width=150) (actual time=0.057..0.057 rows=0 loops=1)

4. 0.015 0.055 ↓ 0.0 0 1

Sort (cost=32.47..32.48 rows=1 width=150) (actual time=0.055..0.055 rows=0 loops=1)

  • Sort Key: geo_closure_leaf.descendant, leaf_info.parent_id
  • Sort Method: quicksort Memory: 25kB
5. 0.001 0.040 ↓ 0.0 0 1

Nested Loop (cost=12.16..32.46 rows=1 width=150) (actual time=0.040..0.040 rows=0 loops=1)

6. 0.004 0.039 ↓ 0.0 0 1

Hash Join (cost=12.03..31.02 rows=6 width=150) (actual time=0.039..0.039 rows=0 loops=1)

  • Hash Cond: ((leaf_info.id)::text = (geo_closure_leaf.descendant)::text)
7. 0.035 0.035 ↓ 0.0 0 1

Seq Scan on geo_clousre leaf_info (cost=0.00..16.50 rows=650 width=64) (actual time=0.035..0.035 rows=0 loops=1)

8. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.00..12.00 rows=2 width=118) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Seq Scan on geo_closure_leaf (cost=0.00..12.00 rows=2 width=118) (never executed)

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

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

  • Index Cond: ((id)::text = (leaf_info.parent_id)::text)
  • Filter: ((geography_level_id)::text = '4'::text)
11.          

CTE curr

12. 0.004 0.083 ↓ 0.0 0 1

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

  • Group Key: filtered_leaf_geos.parent_id
13. 0.020 0.079 ↓ 0.0 0 1

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

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

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

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

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

16. 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))
17.          

CTE prev

18. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Sort Key: b.parent_id
20. 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)
21. 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)

22. 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))
23.          

CTE suggs_at_geo

24. 0.001 0.086 ↓ 0.0 0 1

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

25. 0.001 0.085 ↓ 0.0 0 1

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

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

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

27. 0.000 0.000 ↓ 0.0 0

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

28. 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)