explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1JQ3 : no-idx

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

Nested Loop (cost=51,562,745.27..638,600,238.15 rows=925,599 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=51,562,728.89..637,785,099.83 rows=465,125 width=149) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=51,562,728.89..637,782,774.20 rows=465,125 width=149) (actual rows= loops=)

  • Hash Cond: (a.mpid = cpla.mpid)
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=37,743,888.43..621,634,070.78 rows=465,949 width=40) (actual rows= loops=)

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

Merge Join (cost=37,743,885.18..621,627,660.51 rows=466,010 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))))
18. 0.000 0.000 ↓ 0.0

Sort (cost=130.79..133.21 rows=967 width=20) (actual rows= loops=)

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

Hash Join (cost=20.07..82.84 rows=967 width=20) (actual rows= loops=)

  • Hash Cond: (s.mls_system_id = broker.immed_source_mls_system_id)
20. 0.000 0.000 ↓ 0.0

Seq Scan on mls_system s (cost=0.00..38.64 rows=964 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=16.86..16.86 rows=257 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

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

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

Materialize (cost=37,743,754.39..37,960,506.11 rows=43,350,343 width=40) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=37,743,754.39..37,852,130.25 rows=43,350,343 width=40) (actual rows= loops=)

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

Hash Join (cost=17,401,453.81..31,059,500.57 rows=43,350,343 width=40) (actual rows= loops=)

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

Nested Loop (cost=919,777.59..5,920,653.18 rows=43,350,343 width=40) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on mp_activity_mls_mv a (cost=919,777.59..5,487,149.73 rows=43,350,343 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)
29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mp_activity_mls_mv_1 (cost=0.00..908,940.00 rows=43,350,343 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))
30. 0.000 0.000 ↓ 0.0

Hash (cost=14,373,751.32..14,373,751.32 rows=128,483,032 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on mp_list list (cost=0.00..14,373,751.32 rows=128,483,032 width=8) (actual rows= loops=)

32.          

SubPlan (for Hash Join)

33. 0.000 0.000 ↓ 0.0

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

34. 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)
35. 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)
36. 0.000 0.000 ↓ 0.0

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

37. 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=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=9,982,165.04..9,982,165.04 rows=127,556,993 width=117) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on customer_property_list_address_13_1 cpla (cost=0.00..9,982,165.04 rows=127,556,993 width=117) (actual rows= loops=)

  • 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))