explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rEcB

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

Update on zip_alignment_stage stage (cost=73.13..89.41 rows=1 width=1,987) (actual rows= loops=)

2.          

CTE cte

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..18.92 rows=1 width=524) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on team t (cost=0.00..10.75 rows=1 width=532) (actual rows= loops=)

  • Filter: ((superseded IS NULL) AND (request_id = 0))
5. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_temp_stage_teamid_effdate on temp_stage stage_1 (cost=0.14..8.16 rows=1 width=520) (actual rows= loops=)

  • Index Cond: ((team_id = (t.team_id)::text) AND (effective_date >= t.start_date) AND (effective_date < t.end_date))
6.          

CTE cte1

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=10.68..23.08 rows=3 width=524) (actual rows= loops=)

  • Hash Cond: ((g.geography_id)::text = (stage_2.geography_id)::text)
  • Join Filter: ((g.start_date <= stage_2.effective_date) AND (g.end_date > stage_2.effective_date))
8. 0.000 0.000 ↓ 0.0

Seq Scan on geographies g (cost=0.00..11.50 rows=120 width=26) (actual rows= loops=)

  • Filter: ((superseded IS NULL) AND (request_id = 0))
9. 0.000 0.000 ↓ 0.0

Hash (cost=10.30..10.30 rows=30 width=520) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on temp_stage stage_2 (cost=0.00..10.30 rows=30 width=520) (actual rows= loops=)

11.          

CTE cte2

12. 0.000 0.000 ↓ 0.0

Hash Join (cost=10.68..30.85 rows=3 width=524) (actual rows= loops=)

  • Hash Cond: ((z.zip_code)::text = (stage_3.zip_code)::text)
  • Join Filter: ((z.start_date <= stage_3.effective_date) AND (z.end_date > stage_3.effective_date))
13. 0.000 0.000 ↓ 0.0

Seq Scan on zip z (cost=0.00..17.50 rows=590 width=21) (actual rows= loops=)

  • Filter: ((superseded IS NULL) AND (request_id = 0))
14. 0.000 0.000 ↓ 0.0

Hash (cost=10.30..10.30 rows=30 width=520) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on temp_stage stage_3 (cost=0.00..10.30 rows=30 width=520) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..16.56 rows=1 width=1,987) (actual rows= loops=)

  • Join Filter: ((ts.zip_code)::text = (cte2.zip_code)::text)
17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..16.46 rows=1 width=1,943) (actual rows= loops=)

  • Join Filter: ((ts.geography_id)::text = (cte1.geography_id)::text)
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..16.36 rows=1 width=1,903) (actual rows= loops=)

  • Join Filter: ((ts.team_id)::text = (cte.team_id)::text)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..16.33 rows=1 width=1,863) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Index Scan using idx_zip_alignment_stage_requestid on zip_alignment_stage stage (cost=0.14..8.16 rows=1 width=309) (actual rows= loops=)

  • Index Cond: (request_id = 0)
21. 0.000 0.000 ↓ 0.0

Index Scan using idx_temp_stage_zip_code_effdate on temp_stage ts (cost=0.14..8.16 rows=1 width=1,554) (actual rows= loops=)

  • Index Cond: ((zip_code)::text = (stage.zip_code)::text)
  • Filter: (((stage.team_id)::text = (team_id)::text) AND ((stage.geography_id)::text = (geography_id)::text))
22. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..0.02 rows=1 width=1,072) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

CTE Scan on cte1 (cost=0.00..0.06 rows=3 width=1072)" -> CTE Scan on cte2 (cost=0.00..0.06 rows=3 width=1,072) (actual rows= loops=)