explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hqlZ : one-liner partition

Settings
# exclusive inclusive rows x rows loops node
1. 115,842.806 14,111,158.678 ↓ 24.2 59,965,876 1

Nested Loop (cost=35,496,817.13..483,173,471.61 rows=2,480,697 width=209) (actual time=5,736,941.805..14,111,158.678 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.136 4.470 ↑ 1.0 1 1

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

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

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

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

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

CTE status_fltr

7. 0.022 6.041 ↑ 100.0 1 1

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

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

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

9. 0.008 1.540 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 1.532 1.532 ↑ 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.531..1.532 rows=10 loops=1)

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

CTE list_type_fltr

12. 0.011 0.011 ↑ 100.0 1 1

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

13. 6.044 6.044 ↑ 100.0 1 1

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

14. 99,516.225 13,995,309.828 ↓ 112.3 140,013,545 1

Materialize (cost=35,496,801.99..480,988,850.69 rows=1,246,582 width=149) (actual time=5,736,928.457..13,995,309.828 rows=140,013,545 loops=1)

15. 58,490.089 13,895,793.603 ↓ 112.3 140,013,545 1

Hash Join (cost=35,496,801.99..480,982,617.78 rows=1,246,582 width=149) (actual time=5,736,928.453..13,895,793.603 rows=140,013,545 loops=1)

  • Hash Cond: (a.list_type_cd = ltf.cust_list_type_cds)
16. 56,825.614 13,837,303.501 ↓ 125.1 155,961,072 1

Merge Join (cost=35,496,798.74..480,965,472.95 rows=1,246,872 width=153) (actual time=5,736,928.411..13,837,303.501 rows=155,961,072 loops=1)

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

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

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

Seq Scan on mls_system_agent broker (cost=0.00..16.68 rows=249 width=8) (actual time=1.517..10.704 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
19. 79,682.299 13,780,466.809 ↓ 124.3 156,134,018 1

Materialize (cost=35,496,772.15..591,116,618.71 rows=1,255,908 width=153) (actual time=5,736,917.624..13,780,466.809 rows=156,134,018 loops=1)

20. 244,366.020 13,700,784.510 ↓ 124.3 156,134,018 1

Nested Loop (cost=35,496,772.15..591,113,478.94 rows=1,255,908 width=153) (actual time=5,736,917.616..13,700,784.510 rows=156,134,018 loops=1)

21. 67,962.913 5,955,608.734 ↓ 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,736,908.802..5,955,608.734 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.663 34.263 ↓ 1.0 953 1

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

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

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

24. 25,627.585 5,887,611.558 ↓ 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,736,874.861..5,887,611.558 rows=170,472,949 loops=1)

25. 257,082.564 5,861,983.973 ↓ 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,736,874.856..5,861,983.973 rows=170,472,949 loops=1)

  • Sort Key: list.immed_source_mls_system_id, ((SubPlan 4))
  • Sort Method: external merge Disk: 10831872kB
26. 174,872.313 5,604,901.409 ↓ 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=409,351.836..5,604,901.409 rows=173,962,416 loops=1)

  • Hash Cond: (a.data_source_record_id = list.list_id)
27. 34,433.267 4,210,118.743 ↓ 4.2 173,962,416 1

Nested Loop (cost=870,594.88..5,603,155.04 rows=41,030,305 width=40) (actual time=59,013.413..4,210,118.743 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,118,205.561 4,175,685.474 ↓ 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=59,013.409..4,175,685.474 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. 57,479.913 57,479.913 ↓ 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=57,479.913..57,479.913 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. 26,217.119 350,098.273 ↓ 1.0 122,964,466 1

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

  • Buckets: 67108864 Batches: 4 Memory Usage: 1725089kB
32. 323,881.154 323,881.154 ↓ 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.453..323,881.154 rows=122,964,466 loops=1)

33.          

SubPlan (for Hash Join)

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

35. 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
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. 7,500,809.756 7,500,809.756 ↑ 3.0 1 170,472,949

Index Only Scan using cpla_national_customer_1 on cpla_national_customer cpla (cost=0.70..6.70 rows=3 width=117) (actual time=0.044..0.044 rows=1 loops=170,472,949)

  • Index Cond: ((customer_configuration_id = 29) AND (property_list_id = 1) AND (mpid = a.mpid) AND (cust_supplied_address_seq = 1) AND (property_list_address_effective_dt <= ('now'::cstring)::date) AND (property_list_address_end_dt > ('now'::cstring)::date))
  • Heap Fetches: 118449302
38. 0.002 0.013 ↑ 100.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.011 0.011 ↑ 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.011 rows=1 loops=1)

Planning time : 177.941 ms
Execution time : 14,119,561.911 ms