explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kvdH : with-idx

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

Nested Loop (cost=37,425,657.62..620,185,301.91 rows=900,512 width=209) (actual rows= loops=)

  • Join Filter: ((sf.status_nm = 'All'::text) OR (sf.property_activity_sub_type_cd = a.property_activity_sub_type_cd))
2.          

CTE hdr_info

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.27..12.00 rows=1 width=133) (actual rows= loops=)

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=128) (actual rows= loops=)

  • 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.00..3.70 rows=1 width=8) (actual rows= loops=)

  • Filter: (customer_configuration_id = 13)
6.          

CTE status_fltr

7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.36..3.86 rows=100 width=34) (actual rows= loops=)

  • Hash Cond: ((unnest(hdr_info.status_list_txt)) = (pastc.property_activity_sub_type_nm)::text)
8. 0.000 0.000 ↓ 0.0

CTE Scan on hdr_info (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=1.24..1.24 rows=10 width=10) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on property_activity_sub_type_code pastc (cost=0.00..1.24 rows=10 width=10) (actual rows= loops=)

  • 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.00..0.52 rows=100 width=4) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

CTE Scan on status_fltr sf (cost=0.00..2.00 rows=100 width=34) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Materialize (cost=37,425,641.24..619,392,257.04 rows=452,518 width=149) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=37,425,641.24..619,389,994.45 rows=452,518 width=149) (actual rows= loops=)

  • Hash Cond: (a.list_type_cd = ltf.cust_list_type_cds)
16. 0.000 0.000 ↓ 0.0

Merge Join (cost=37,425,637.99..619,383,768.77 rows=452,595 width=153) (actual rows= loops=)

  • Merge Cond: (broker.immed_source_mls_system_id = s.mls_system_id)
17. 0.000 0.000 ↓ 0.0

Sort (cost=27.07..27.71 rows=256 width=8) (actual rows= loops=)

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

Seq Scan on mls_system_agent broker (cost=0.00..16.83 rows=256 width=8) (actual rows= loops=)

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

Materialize (cost=37,425,610.92..621,266,174.13 rows=451,155 width=153) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=37,425,610.92..621,265,046.24 rows=451,155 width=153) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Merge Join (cost=37,425,610.35..619,227,849.78 rows=452,240 width=44) (actual rows= loops=)

  • Merge Cond: ((s.mls_system_id = list.immed_source_mls_system_id) AND (s.mls_system_end_dt = ((SubPlan 4))))
22. 0.000 0.000 ↓ 0.0

Sort (cost=86.29..88.69 rows=962 width=12) (actual rows= loops=)

  • 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.00..38.62 rows=962 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Materialize (cost=37,425,524.06..37,641,911.04 rows=43,277,395 width=40) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=37,425,524.06..37,533,717.55 rows=43,277,395 width=40) (actual rows= loops=)

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

Hash Join (cost=17,233,553.62..30,753,044.29 rows=43,277,395 width=40) (actual rows= loops=)

  • Hash Cond: (a.data_source_record_id = list.list_id)
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=918,336.29..5,911,838.82 rows=43,277,395 width=40) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

CTE Scan on hdr_info hi (cost=0.00..0.02 rows=1 width=28) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mp_activity_mls_mv a (cost=918,336.29..5,479,064.85 rows=43,277,395 width=36) (actual rows= loops=)

  • Recheck Cond: ((value_eff_dt >= hi.initial_delivery_back_dt) AND (value_eff_dt <= hi.initial_delivery_back_end_dt))
  • Filter: (mpid IS NOT NULL)
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mp_activity_mls_mv_1 (cost=0.00..907,516.94 rows=43,278,837 width=0) (actual rows= loops=)

  • 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,246,210.59..14,246,210.59 rows=126,110,859 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on mp_list list (cost=0.00..14,246,210.59 rows=126,110,859 width=8) (actual rows= loops=)

33.          

SubPlan (for Hash Join)

34. 0.000 0.000 ↓ 0.0

Aggregate (cost=13.45..13.46 rows=1 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mls_system ss (cost=4.30..13.44 rows=3 width=8) (actual rows= loops=)

  • Recheck Cond: (mls_system_id = list.immed_source_mls_system_id)
36. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mls_system_1 (cost=0.00..4.30 rows=3 width=0) (actual rows= loops=)

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

Index Scan using customer_property_list_address_13_1_4 on customer_property_list_address_13_1 cpla (cost=0.57..4.49 rows=1 width=117) (actual rows= loops=)

  • Index Cond: (mpid = a.mpid)
  • Filter: ((customer_configuration_id = 13) AND (property_list_id = 1) AND (cust_supplied_address_seq = 1) AND ((cust_supplied_action)::text = 'A'::text) AND (property_list_address_effective_dt <= ('now'::cstring)::date) AND (property_list_address_end_dt > ('now'::cstring)::date))
38. 0.000 0.000 ↓ 0.0

Hash (cost=2.00..2.00 rows=100 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

CTE Scan on list_type_fltr ltf (cost=0.00..2.00 rows=100 width=4) (actual rows= loops=)