explain.depesz.com

PostgreSQL's explain analyze made readable

Result: osID : one-liner tbl

Settings
# exclusive inclusive rows x rows loops node
1. 82,972.659 7,009,039.602 ↓ 12.0 59,965,876 1

Nested Loop (cost=49,579,831.52..504,820,517.72 rows=4,983,412 width=209) (actual time=6,270,467.257..7,009,039.602 rows=59,965,876 loops=1)

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

CTE hdr_info

3. 0.131 3.933 ↑ 1.0 1 1

Nested Loop (cost=0.14..10.75 rows=1 width=129) (actual time=3.926..3.933 rows=1 loops=1)

4. 1.890 1.890 ↑ 1.0 1 1

Index Scan using customer_property_list_0 on customer_property_list cpl (cost=0.14..8.16 rows=1 width=124) (actual time=1.888..1.890 rows=1 loops=1)

  • Index Cond: ((customer_configuration_id = 29) AND (property_list_id = 1))
5. 1.912 1.912 ↑ 1.0 1 1

Seq Scan on customer_configuration cc (cost=0.00..2.58 rows=1 width=8) (actual time=1.908..1.912 rows=1 loops=1)

  • Filter: (customer_configuration_id = 29)
  • Rows Removed by Filter: 45
6.          

CTE status_fltr

7. 0.027 4.865 ↑ 100.0 1 1

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

  • Hash Cond: ((unnest(hdr_info.status_list_txt)) = (pastc.property_activity_sub_type_nm)::text)
8. 3.939 3.939 ↑ 100.0 1 1

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

9. 0.004 0.899 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.895 0.895 ↑ 1.0 10 1

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

  • Filter: (property_activity_type_cd = 5)
  • Rows Removed by Filter: 9
11.          

CTE list_type_fltr

12. 0.019 0.019 ↑ 100.0 1 1

CTE Scan on hdr_info hdr_info_1 (cost=0.00..0.52 rows=100 width=4) (actual time=0.011..0.019 rows=1 loops=1)

13. 4.867 4.867 ↑ 100.0 1 1

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

14. 62,158.404 6,926,062.076 ↓ 55.9 140,013,545 1

Materialize (cost=49,579,816.39..500,431,905.37 rows=2,504,227 width=149) (actual time=6,270,462.302..6,926,062.076 rows=140,013,545 loops=1)

15. 228,118.025 6,863,903.672 ↓ 55.9 140,013,545 1

Hash Join (cost=49,579,816.39..500,419,384.24 rows=2,504,227 width=149) (actual time=6,270,462.298..6,863,903.672 rows=140,013,545 loops=1)

  • Hash Cond: (a.mpid = cpla.mpid)
16. 32,047.321 6,344,725.378 ↓ 343.8 151,177,389 1

Hash Join (cost=35,496,801.29..478,570,192.16 rows=439,722 width=40) (actual time=5,979,342.166..6,344,725.378 rows=151,177,389 loops=1)

  • Hash Cond: (a.list_type_cd = ltf.cust_list_type_cds)
17. 47,643.298 6,312,678.032 ↓ 387.1 170,270,440 1

Merge Join (cost=35,496,798.04..478,564,142.35 rows=439,825 width=44) (actual time=5,979,342.084..6,312,678.032 rows=170,270,440 loops=1)

  • Merge Cond: (broker.immed_source_mls_system_id = s.mls_system_id)
18. 0.374 11.355 ↑ 1.0 249 1

Sort (cost=26.59..27.21 rows=249 width=8) (actual time=11.053..11.355 rows=249 loops=1)

  • Sort Key: broker.immed_source_mls_system_id
  • Sort Method: quicksort Memory: 36kB
19. 10.981 10.981 ↑ 1.0 249 1

Seq Scan on mls_system_agent broker (cost=0.00..16.68 rows=249 width=8) (actual time=1.577..10.981 rows=249 loops=1)

  • Filter: (mls_system_agent_end_dt = to_date('9999-12-31'::text, 'yyyy-mm-dd'::text))
  • Rows Removed by Filter: 263
20. 52,466.150 6,265,023.379 ↓ 384.8 170,472,949 1

Materialize (cost=35,496,771.45..588,133,685.46 rows=443,012 width=44) (actual time=5,979,331.028..6,265,023.379 rows=170,472,949 loops=1)

21. 62,838.000 6,212,557.229 ↓ 384.8 170,472,949 1

Merge Join (cost=35,496,771.45..588,132,577.93 rows=443,012 width=44) (actual time=5,979,331.022..6,212,557.229 rows=170,472,949 loops=1)

  • Merge Cond: ((s.mls_system_id = list.immed_source_mls_system_id) AND (s.mls_system_end_dt = ((SubPlan 4))))
22. 0.717 33.813 ↓ 1.0 953 1

Sort (cost=85.62..88.00 rows=952 width=12) (actual time=33.429..33.813 rows=953 loops=1)

  • Sort Key: s.mls_system_id, s.mls_system_end_dt
  • Sort Method: quicksort Memory: 69kB
23. 33.096 33.096 ↓ 1.0 953 1

Seq Scan on mls_system s (cost=0.00..38.52 rows=952 width=12) (actual time=3.680..33.096 rows=953 loops=1)

24. 18,889.042 6,149,685.416 ↓ 4.2 170,472,949 1

Materialize (cost=35,496,685.83..35,701,837.35 rows=41,030,305 width=40) (actual time=5,979,297.588..6,149,685.416 rows=170,472,949 loops=1)

25. 322,784.893 6,130,796.374 ↓ 4.2 170,472,949 1

Sort (cost=35,496,685.83..35,599,261.59 rows=41,030,305 width=40) (actual time=5,979,297.584..6,130,796.374 rows=170,472,949 loops=1)

  • Sort Key: list.immed_source_mls_system_id, ((SubPlan 4))
  • Sort Method: external merge Disk: 10831872kB
26. 272,135.194 5,808,011.481 ↓ 4.2 173,962,416 1

Hash Join (cost=16,621,830.88..29,186,440.49 rows=41,030,305 width=40) (actual time=391,780.054..5,808,011.481 rows=173,962,416 loops=1)

  • Hash Cond: (a.data_source_record_id = list.list_id)
27. 35,408.451 4,162,532.123 ↓ 4.2 173,962,416 1

Nested Loop (cost=870,594.88..5,603,155.04 rows=41,030,305 width=40) (actual time=61,981.530..4,162,532.123 rows=173,962,416 loops=1)

28. 0.002 0.002 ↑ 1.0 1 1

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

29. 4,066,671.204 4,127,123.670 ↓ 4.2 173,962,416 1

Bitmap Heap Scan on mp_activity_mls_mv a (cost=870,594.88..5,192,851.97 rows=41,030,305 width=36) (actual time=61,981.524..4,127,123.670 rows=173,962,416 loops=1)

  • 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)
  • Rows Removed by Filter: 11242
  • Heap Blocks: exact=3618991
30. 60,452.466 60,452.466 ↓ 4.2 173,973,658 1

Bitmap Index Scan on mp_activity_mls_mv_1 (cost=0.00..860,337.30 rows=41,031,673 width=0) (actual time=60,452.466..60,452.466 rows=173,973,658 loops=1)

  • Index Cond: ((value_eff_dt >= hi.initial_delivery_back_dt) AND (value_eff_dt <= hi.initial_delivery_back_end_dt))
31. 27,508.930 329,569.668 ↓ 1.0 122,964,466 1

Hash (cost=13,767,841.78..13,767,841.78 rows=120,892,578 width=8) (actual time=329,569.668..329,569.668 rows=122,964,466 loops=1)

  • Buckets: 67108864 Batches: 4 Memory Usage: 1725089kB
32. 302,060.738 302,060.738 ↓ 1.0 122,964,466 1

Seq Scan on mp_list list (cost=0.00..13,767,841.78 rows=120,892,578 width=8) (actual time=6.202..302,060.738 rows=122,964,466 loops=1)

33.          

SubPlan (for Hash Join)

34. 347,924.832 1,043,774.496 ↑ 1.0 1 173,962,416

Aggregate (cost=13.45..13.46 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=173,962,416)

35. 347,924.832 695,849.664 ↓ 3.0 9 173,962,416

Bitmap Heap Scan on mls_system ss (cost=4.30..13.44 rows=3 width=8) (actual time=0.003..0.004 rows=9 loops=173,962,416)

  • Recheck Cond: (mls_system_id = list.immed_source_mls_system_id)
  • Heap Blocks: exact=304315219
36. 347,924.832 347,924.832 ↓ 3.3 10 173,962,416

Bitmap Index Scan on mls_system_1 (cost=0.00..4.30 rows=3 width=0) (actual time=0.002..0.002 rows=10 loops=173,962,416)

  • Index Cond: (mls_system_id = list.immed_source_mls_system_id)
37. 0.003 0.025 ↑ 100.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.022 0.022 ↑ 100.0 1 1

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

39. 39,546.036 291,060.269 ↑ 1.1 110,152,092 1

Hash (cost=10,474,078.56..10,474,078.56 rows=119,985,403 width=117) (actual time=291,060.269..291,060.269 rows=110,152,092 loops=1)

  • Buckets: 16777216 Batches: 16 Memory Usage: 630571kB
40. 251,514.233 251,514.233 ↑ 1.0 119,782,580 1

Seq Scan on cpla_national_customer_2 cpla (cost=0.00..10,474,078.56 rows=119,985,403 width=117) (actual time=5.251..251,514.233 rows=119,782,580 loops=1)

  • Filter: ((customer_configuration_id = 29) 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))
  • Rows Removed by Filter: 16299621
Planning time : 4.250 ms
Execution time : 7,013,797.780 ms