explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R3E9 : zillow explain BEFORE

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

Nested Loop (cost=36,589,212.17..603,191,646.65 rows=891,463 width=237) (never executed)

2.          

CTE hdr_info

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.27..11.97 rows=1 width=130) (never executed)

4. 0.000 0.000 ↓ 0.0

Index Scan using customer_property_list_0 on customer_property_list cpl (cost=0.27..8.29 rows=1 width=125) (never executed)

  • Index Cond: ((customer_configuration_id = 13) AND (property_list_id = 1))
5. 0.000 0.000 ↓ 0.0

Seq Scan on customer_configuration cc (cost=0..3.66 rows=1 width=8) (never executed)

  • Filter: (customer_configuration_id = 13)
6.          

CTE status_fltr

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.36..3.86 rows=100 width=34) (never executed)

8. 0.000 0.000 ↓ 0.0

CTE Scan on hdr_info hdr_info (cost=0..0.52 rows=100 width=32) (never executed)

9. 0.000 0.000 ↓ 0.0

Hash (cost=1.24..1.24 rows=10 width=10) (never executed)

10. 0.000 0.000 ↓ 0.0

Seq Scan on property_activity_sub_type_code pastc (cost=0..1.24 rows=10 width=10) (never executed)

  • Filter: (property_activity_type_cd = 5)
11.          

CTE list_type_fltr

12. 0.000 0.000 ↓ 0.0

CTE Scan on hdr_info hdr_info_1 (cost=0..0.52 rows=100 width=4) (never executed)

13. 0.000 0.000 ↓ 0.0

CTE Scan on status_fltr sf (cost=0..2 rows=100 width=34) (never executed)

14. 0.000 0.000 ↓ 0.0

Materialize (cost=36,589,195.82..602,406,568.57 rows=447,972 width=149) (never executed)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=36,589,195.82..602,404,328.71 rows=447,972 width=149) (never executed)

16. 0.000 0.000 ↓ 0.0

Hash Join (cost=36,589,195.26..602,099,360.78 rows=448,555 width=40) (never executed)

17. 0.000 0.000 ↓ 0.0

Merge Join (cost=36,589,192.01..602,093,189.73 rows=448,600 width=44) (never executed)

18. 0.000 0.000 ↓ 0.0

Sort (cost=26.74..27.37 rows=251 width=8) (never executed)

  • Sort Key: broker.immed_source_mls_system_id
19. 0.000 0.000 ↓ 0.0

Seq Scan on mls_system_agent broker (cost=0..16.74 rows=251 width=8) (never executed)

  • Filter: (mls_system_agent_end_dt = to_date('9999-12-31'::text, 'yyyy-mm-dd'::text))
20. 0.000 0.000 ↓ 0.0

Materialize (cost=36,589,165.26..603,887,420.99 rows=450,089 width=44) (never executed)

21. 0.000 0.000 ↓ 0.0

Merge Join (cost=36,589,165.26..603,886,295.77 rows=450,089 width=44) (never executed)

22. 0.000 0.000 ↓ 0.0

Sort (cost=85.89..88.28 rows=956 width=12) (never executed)

  • Sort Key: s.mls_system_id, s.mls_system_end_dt
23. 0.000 0.000 ↓ 0.0

Seq Scan on mls_system s (cost=0..38.56 rows=956 width=12) (never executed)

24. 0.000 0.000 ↓ 0.0

Materialize (cost=36,589,079.38..36,799,673.67 rows=42,118,859 width=40) (never executed)

25. 0.000 0.000 ↓ 0.0

Sort (cost=36,589,079.38..36,694,376.52 rows=42,118,859 width=40) (never executed)

  • Sort Key: list.immed_source_mls_system_id, ((SubPlan 4))
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=17,104,993.06..30,103,465.86 rows=42,118,859 width=40) (never executed)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=893,618.88..5,752,237.37 rows=42,118,859 width=40) (never executed)

28. 0.000 0.000 ↓ 0.0

CTE Scan on hdr_info hi (cost=0..0.02 rows=1 width=28) (never executed)

29. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mp_activity_mls_mv a (cost=893,618.88..5,331,048.76 rows=42,118,859 width=36) (never executed)

  • Filter: (mpid IS NOT NULL)
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mp_activity_mls_mv_1 (cost=0..883,089.16 rows=42,118,859 width=0) (never executed)

  • Index Cond: ((value_eff_dt >= hi.initial_delivery_back_dt) AND (value_eff_dt <= hi.initial_delivery_back_end_dt))
31. 0.000 0.000 ↓ 0.0

Hash (cost=14,197,533.08..14,197,533.08 rows=122,748,408 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0

Seq Scan on mp_list list (cost=0..14,197,533.08 rows=122,748,408 width=8) (never executed)

33.          

SubPlan (for Hash Join)

34. 0.000 0.000 ↓ 0.0

Aggregate (cost=13.45..13.46 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mls_system ss (cost=4.3..13.44 rows=3 width=8) (never executed)

36. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mls_system_1 (cost=0..4.3 rows=3 width=0) (never executed)

  • Index Cond: (mls_system_id = list.immed_source_mls_system_id)
37. 0.000 0.000 ↓ 0.0

Hash (cost=2..2 rows=100 width=4) (never executed)

38. 0.000 0.000 ↓ 0.0

CTE Scan on list_type_fltr ltf (cost=0..2 rows=100 width=4) (never executed)

39. 0.000 0.000 ↓ 0.0

Index Scan using customer_property_list_address_13_1_1 on customer_property_list_address_13_1 cpla (cost=0.57..0.67 rows=1 width=117) (never executed)

  • Index Cond: (mpid = a.mpid)
  • Filter: ((customer_configuration_id = 13) AND (property_list_id = 1) AND (cust_supplied_address_seq = 1) AND (property_list_address_effective_dt <= ('now'::cstring)::date) AND (property_list_address_end_dt > ('now'::cstring)::date))