explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OChh : one-liner partition

Settings
# exclusive inclusive rows x rows loops node
1. 153,545.573 6,775,580.530 ↓ 1.9 59,965,876 1

Hash Join (cost=479,351,760.18..503,671,556.37 rows=31,235,279 width=209) (actual time=6,328,675.911..6,775,580.530 rows=59,965,876 loops=1)

  • Hash Cond: (cpla.mpid = a.mpid)
2.          

CTE hdr_info

3. 0.139 5.939 ↑ 1.0 1 1

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

4. 2.711 2.711 ↑ 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=2.711..2.711 rows=1 loops=1)

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

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

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

CTE status_fltr

7. 0.024 7.355 ↑ 100.0 1 1

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

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

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

9. 0.009 1.384 ↑ 1.0 10 1

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

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

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

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

CTE list_type_fltr

12. 0.009 0.009 ↑ 100.0 1 1

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

13. 7,670.900 293,370.242 ↓ 1.0 119,782,580 1

Append (cost=0.00..10,474,514.60 rows=119,598,584 width=117) (actual time=5.096..293,370.242 rows=119,782,580 loops=1)

14. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on customer_property_list_address cpla (cost=0.00..0.00 rows=1 width=640) (actual time=0.001..0.001 rows=0 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))
15. 285,699.341 285,699.341 ↓ 1.0 119,782,580 1

Seq Scan on cpla_cc_29_pl_1 cpla_1 (cost=0.00..10,474,514.60 rows=119,598,583 width=117) (actual time=5.095..285,699.341 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
16. 28,330.768 6,328,664.715 ↓ 74.4 65,074,709 1

Hash (cost=479,340,806.97..479,340,806.97 rows=875,046 width=40) (actual time=6,328,664.715..6,328,664.715 rows=65,074,709 loops=1)

  • Buckets: 33554432 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 1835009kB
17. 25,926.835 6,300,333.947 ↓ 74.4 65,074,709 1

Nested Loop (cost=35,496,801.29..479,340,806.97 rows=875,046 width=40) (actual time=5,889,109.785..6,300,333.947 rows=65,074,709 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: 86102680
18. 7.358 7.358 ↑ 100.0 1 1

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

19. 63,712.145 6,274,399.754 ↓ 343.8 151,177,389 1

Materialize (cost=35,496,801.29..478,572,390.77 rows=439,722 width=40) (actual time=5,889,102.422..6,274,399.754 rows=151,177,389 loops=1)

20. 32,835.882 6,210,687.609 ↓ 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,889,102.419..6,210,687.609 rows=151,177,389 loops=1)

  • Hash Cond: (a.list_type_cd = ltf.cust_list_type_cds)
21. 48,833.657 6,177,851.713 ↓ 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,889,102.377..6,177,851.713 rows=170,270,440 loops=1)

  • Merge Cond: (broker.immed_source_mls_system_id = s.mls_system_id)
22. 0.352 9.736 ↑ 1.0 249 1

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

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

Seq Scan on mls_system_agent broker (cost=0.00..16.68 rows=249 width=8) (actual time=1.244..9.384 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
24. 51,533.971 6,129,008.320 ↓ 384.8 170,472,949 1

Materialize (cost=35,496,771.45..588,133,685.46 rows=443,012 width=44) (actual time=5,889,092.915..6,129,008.320 rows=170,472,949 loops=1)

25. 65,987.093 6,077,474.349 ↓ 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,889,092.910..6,077,474.349 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))))
26. 0.666 0.858 ↓ 1.0 953 1

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

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

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

28. 18,127.254 6,011,486.398 ↓ 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,889,092.421..6,011,486.398 rows=170,472,949 loops=1)

29. 236,555.019 5,993,359.144 ↓ 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,889,092.414..5,993,359.144 rows=170,472,949 loops=1)

  • Sort Key: list.immed_source_mls_system_id, ((SubPlan 4))
  • Sort Method: external merge Disk: 10831872kB
30. 186,634.295 5,756,804.125 ↓ 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=539,931.704..5,756,804.125 rows=173,962,416 loops=1)

  • Hash Cond: (a.data_source_record_id = list.list_id)
31. 32,083.388 4,255,068.694 ↓ 4.2 173,962,416 1

Nested Loop (cost=870,594.88..5,603,155.04 rows=41,030,305 width=40) (actual time=94,406.764..4,255,068.694 rows=173,962,416 loops=1)

32. 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)

33. 4,130,088.750 4,222,985.304 ↓ 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=94,406.759..4,222,985.304 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
34. 92,896.554 92,896.554 ↓ 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=92,896.554..92,896.554 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))
35. 25,895.686 445,289.056 ↓ 1.0 122,964,466 1

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

  • Buckets: 67108864 Batches: 4 Memory Usage: 1725089kB
36. 419,393.370 419,393.370 ↓ 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=5.379..419,393.370 rows=122,964,466 loops=1)

37.          

SubPlan (for Hash Join)

38. 347,924.832 869,812.080 ↑ 1.0 1 173,962,416

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

39. 173,962.416 521,887.248 ↓ 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.002..0.003 rows=9 loops=173,962,416)

  • Recheck Cond: (mls_system_id = list.immed_source_mls_system_id)
  • Heap Blocks: exact=304315219
40. 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)
41. 0.004 0.014 ↑ 100.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.010 0.010 ↑ 100.0 1 1

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

Planning time : 194.004 ms
Execution time : 6,780,701.318 ms