explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CvUE

Settings
# exclusive inclusive rows x rows loops node
1. 9.127 17,852.874 ↓ 1,223.0 1,223 1

Sort (cost=1,196.97..1,196.98 rows=1 width=1,140) (actual time=17,852.708..17,852.874 rows=1,223 loops=1)

  • Sort Key: l.property_name, xmax.considered_rented_on DESC, (CASE WHEN (l.lease_interval_type_id = 1) THEN 1 WHEN (l.lease_interval_type_id = 5) THEN 2 WHEN (l.lease_interval_type_id = 3) THEN 3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 679kB
2. 33.783 17,843.747 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=277.42..1,196.96 rows=1 width=1,140) (actual time=214.129..17,843.747 rows=1,223 loops=1)

  • Filter: CASE WHEN (3 = l.lease_interval_type_id) THEN (((cal_max.lease_approved)::date >= '2018-01-01'::date) AND ((cal_max.lease_approved)::date < '2019-01-01 00:00:00'::timestamp without time zone)) WHEN (1 = l.lease_interval_type_id) THEN CASE WHEN ('12'::text = pp_applications.value) THEN (((l.application_approved_on)::date >= '2018-01-01'::date) AND ((l.application_approved_on)::date < '2019-01-01 00:00:00'::timestamp without time zone)) WHEN ('18'::text = pp_applications.value) THEN (((cal_max.lease_approved)::date >= '2018-01-01'::date) AND ((cal_max.lease_approved)::date < '2019-01-01 00:00:00'::timestamp without time zone)) ELSE (((LEAST(l.application_completed, e.event_datetime))::date >= '2018-01-01'::date) AND ((LEAST(l.application_completed, e.event_datetime))::date < '2019-01-01 00:00:00'::timestamp without time zone)) END WHEN (5 = l.lease_interval_type_id) THEN CASE WHEN ('51'::text = pp_transfers.value) THEN (((LEAST(l.application_completed, e.event_datetime))::date >= '2018-01-01'::date) AND ((LEAST(l.application_completed, e.event_datetime))::date < '2019-01-01 00:00:00'::timestamp without time zone)) WHEN ('52'::text = pp_transfers.value) THEN (((l.application_approved_on)::date >= '2018-01-01'::date) AND ((l.application_approved_on)::date < '2019-01-01 00:00:00'::timestamp without time zone)) ELSE (((cal_max.lease_approved)::date >= '2018-01-01'::date) AND ((cal_max.lease_approved)::date < '2019-01-01 00:00:00'::timestamp without time zone)) END ELSE NULL::boolean END
  • Rows Removed by Filter: 770
3. 4.858 17,796.013 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=277.00..1,187.61 rows=1 width=1,101) (actual time=24.038..17,796.013 rows=1,993 loops=1)

4. 5.036 17,773.218 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=276.58..1,178.94 rows=1 width=1,082) (actual time=24.016..17,773.218 rows=1,993 loops=1)

5. 4.428 17,758.217 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=276.29..1,170.89 rows=1 width=1,082) (actual time=24.003..17,758.217 rows=1,993 loops=1)

6. 5.020 17,745.817 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=276.01..1,165.67 rows=1 width=848) (actual time=23.985..17,745.817 rows=1,993 loops=1)

7. 4.332 17,732.825 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=275.73..1,160.90 rows=1 width=843) (actual time=23.973..17,732.825 rows=1,993 loops=1)

8. 5.216 17,722.514 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=275.59..1,160.38 rows=1 width=831) (actual time=23.964..17,722.514 rows=1,993 loops=1)

9. 3.789 17,330.656 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=260.32..1,144.04 rows=1 width=697) (actual time=23.747..17,330.656 rows=1,993 loops=1)

10. 2.527 17,318.895 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=260.04..1,135.72 rows=1 width=561) (actual time=23.735..17,318.895 rows=1,993 loops=1)

11. 642.912 17,300.424 ↓ 1,993.0 1,993 1

Nested Loop (cost=251.70..1,127.36 rows=1 width=557) (actual time=23.721..17,300.424 rows=1,993 loops=1)

  • Join Filter: (cll.unit_space_id = past_recent_lease.unit_space_id)
  • Rows Removed by Join Filter: 2881658
12. 4.758 166.076 ↓ 719.3 2,158 1

Nested Loop Left Join (cost=60.27..385.04 rows=3 width=497) (actual time=6.450..166.076 rows=2,158 loops=1)

13. 4.246 152.686 ↓ 2,158.0 2,158 1

Nested Loop Left Join (cost=59.98..384.72 rows=1 width=501) (actual time=6.438..152.686 rows=2,158 loops=1)

14. 2.798 131.176 ↓ 2,158.0 2,158 1

Nested Loop (cost=59.56..376.26 rows=1 width=481) (actual time=6.423..131.176 rows=2,158 loops=1)

15. 4.937 117.588 ↓ 2,158.0 2,158 1

Nested Loop (cost=59.27..368.94 rows=1 width=477) (actual time=6.397..117.588 rows=2,158 loops=1)

16. 4.094 97.440 ↓ 2,173.0 2,173 1

Nested Loop (cost=58.85..366.68 rows=1 width=477) (actual time=6.380..97.440 rows=2,173 loops=1)

17. 4.494 84.654 ↓ 2,173.0 2,173 1

Nested Loop (cost=58.57..366.37 rows=1 width=483) (actual time=6.370..84.654 rows=2,173 loops=1)

18. 4.300 14.970 ↓ 2,173.0 2,173 1

Hash Join (cost=58.14..357.89 rows=1 width=430) (actual time=6.329..14.970 rows=2,173 loops=1)

  • Hash Cond: ((l.lease_id = xmax.lease_id) AND (l.property_id = xmax.property_id) AND (l.application_id = xmax.application_id))
19. 4.776 4.776 ↓ 49.6 2,181 1

Seq Scan on leads l (cost=0.00..298.75 rows=44 width=414) (actual time=0.422..4.776 rows=2,181 loops=1)

  • Filter: ((unit_number_cache IS NOT NULL) AND (post_date >= '2018-01-01'::date) AND (cid = 8132) AND (lease_interval_type_id = ANY ('{1,5,3}'::integer[])))
  • Rows Removed by Filter: 6659
20. 2.551 5.894 ↓ 112.2 4,935 1

Hash (cost=57.37..57.37 rows=44 width=20) (actual time=5.893..5.894 rows=4,935 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 311kB
21. 2.691 3.343 ↓ 200.9 8,840 1

Bitmap Heap Scan on xyz xmax (cost=4.63..57.37 rows=44 width=20) (actual time=0.672..3.343 rows=8,840 loops=1)

  • Recheck Cond: (cid = 8132)
  • Heap Blocks: exact=52
22. 0.652 0.652 ↓ 200.9 8,840 1

Bitmap Index Scan on idx_temp_xyz_cid (cost=0.00..4.62 rows=44 width=0) (actual time=0.652..0.652 rows=8,840 loops=1)

  • Index Cond: (cid = 8132)
23. 65.190 65.190 ↑ 1.0 1 2,173

Index Scan using idx_cached_lease_logs_cid_lease_id_log_datetime_date on cached_lease_logs cll (cost=0.43..8.47 rows=1 width=53) (actual time=0.030..0.030 rows=1 loops=2,173)

  • Index Cond: ((cid = 8132) AND (lease_id = l.lease_id) AND (reporting_post_date < '2019-01-01 00:00:00'::timestamp without time zone))
  • Filter: ((apply_through_post_date >= '2018-12-31'::date) AND (is_post_date_ignored = 0))
  • Rows Removed by Filter: 13
24. 8.692 8.692 ↑ 1.0 1 2,173

Index Scan using idx_property_charge_settings_property_id on property_charge_settings pcs (cost=0.28..0.30 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=2,173)

  • Index Cond: (property_id = cll.property_id)
  • Filter: (cid = 8132)
25. 15.211 15.211 ↑ 1.0 1 2,173

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..2.25 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=2,173)

  • Index Cond: ((cid = 8132) AND (id = cll.lease_id))
  • Filter: (lease_status_type_id <> 2)
  • Rows Removed by Filter: 0
26. 10.790 10.790 ↑ 1.0 1 2,158

Index Scan using idx_temp_min_max_leads_application_id on min_max_leads cal_max (cost=0.29..7.31 rows=1 width=28) (actual time=0.004..0.005 rows=1 loops=2,158)

  • Index Cond: (application_id = xmax.application_id)
  • Filter: ((cid = 8132) AND (xmax.property_id = property_id))
27. 17.264 17.264 ↑ 1.0 1 2,158

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.43..8.45 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=2,158)

  • Index Cond: (id = l.lease_interval_id)
  • Filter: ((cid = 8132) AND (cid = l.cid) AND (lease_id = l.lease_id))
  • Rows Removed by Filter: 0
28. 8.632 8.632 ↑ 1.0 1 2,158

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..0.32 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,158)

  • Index Cond: ((cid = li.cid) AND (cid = 8132) AND (id = li.lease_term_id))
29. 1,739.348 16,491.436 ↓ 190.9 1,336 2,158

Subquery Scan on past_recent_lease (cost=191.43..247.35 rows=7 width=68) (actual time=3.449..7.642 rows=1,336 loops=2,158)

  • Filter: ((past_recent_lease.lease_id IS NOT NULL) AND (past_recent_lease.cid = 8132) AND (past_recent_lease.prior_lease_row_num = 1))
  • Rows Removed by Filter: 1434
30. 6,465.368 14,752.088 ↓ 2.0 2,771 2,158

WindowAgg (cost=191.43..226.38 rows=1,398 width=80) (actual time=3.448..6.836 rows=2,771 loops=2,158)

31. 4,583.592 8,286.720 ↓ 2.0 2,771 2,158

Sort (cost=191.43..194.93 rows=1,398 width=72) (actual time=3.445..3.840 rows=2,771 loops=2,158)

  • Sort Key: cl_1.unit_space_id, (CASE WHEN (cl_1.move_out_date IS NOT NULL) THEN cl_1.move_out_date ELSE NULL::date END) DESC, cl_1.lease_end_date DESC
  • Sort Method: quicksort Memory: 499kB
32. 3,703.128 3,703.128 ↓ 2.0 2,771 2,158

Seq Scan on available_cached_leases cl_1 (cost=0.00..118.40 rows=1,398 width=72) (actual time=0.005..1.716 rows=2,771 loops=2,158)

  • Filter: ((lease_interval_id <> l.lease_interval_id) AND (lease_start_date < li.lease_start_date) AND (cid = 8132))
  • Rows Removed by Filter: 1423
33. 1.993 15.944 ↓ 0.0 0 1,993

Limit (cost=8.33..8.34 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1,993)

34. 5.979 13.951 ↓ 0.0 0 1,993

Sort (cost=8.33..8.34 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1,993)

  • Sort Key: e.event_datetime
  • Sort Method: quicksort Memory: 25kB
35. 7.972 7.972 ↓ 0.0 0 1,993

Index Scan using idx_events_lease_interval_id_status_change on events e (cost=0.29..8.32 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1,993)

  • Index Cond: (lease_interval_id = l.lease_interval_id)
  • Filter: ((cid = 8132) AND (new_stage_id = 3) AND (new_status_id = 2) AND (((new_stage_id = 3) AND (old_stage_id < 3)) OR ((new_status_id = 2) AND (old_status_id < 2))))
  • Rows Removed by Filter: 0
36. 7.972 7.972 ↑ 1.0 1 1,993

Index Scan using idx_temp_cached_leases_scheduled_charges_lease_interval_id on cached_leases_scheduled_charges lsc (cost=0.29..8.31 rows=1 width=156) (actual time=0.004..0.004 rows=1 loops=1,993)

  • Index Cond: (lease_interval_id = past_recent_lease.lease_interval_id)
  • Filter: ((cid = 8132) AND (cid = past_recent_lease.cid) AND (lease_id = past_recent_lease.lease_id))
37. 89.685 386.642 ↑ 1.0 1 1,993

GroupAggregate (cost=15.27..16.32 rows=1 width=176) (actual time=0.194..0.194 rows=1 loops=1,993)

  • Group Key: sc.one_time_concession
38. 11.958 296.957 ↓ 4.0 4 1,993

Sort (cost=15.27..15.28 rows=1 width=73) (actual time=0.148..0.149 rows=4 loops=1,993)

  • Sort Key: sc.one_time_concession
  • Sort Method: quicksort Memory: 25kB
39. 3.986 284.999 ↓ 4.0 4 1,993

Subquery Scan on sc (cost=15.22..15.26 rows=1 width=73) (actual time=0.134..0.143 rows=4 loops=1,993)

40. 17.937 281.013 ↓ 4.0 4 1,993

WindowAgg (cost=15.22..15.25 rows=1 width=121) (actual time=0.133..0.141 rows=4 loops=1,993)

41. 17.937 263.076 ↓ 4.0 4 1,993

Sort (cost=15.22..15.22 rows=1 width=81) (actual time=0.131..0.132 rows=4 loops=1,993)

  • Sort Key: sc_charges.property_id, sc_charges.ar_origin_id, sc_charges.ar_code_type_id, sc_charges.charge_end_date DESC
  • Sort Method: quicksort Memory: 25kB
42. 9.965 245.139 ↓ 4.0 4 1,993

Group (cost=15.17..15.21 rows=1 width=81) (actual time=0.119..0.123 rows=4 loops=1,993)

  • Group Key: sc_charges.cid, sc_charges.lease_id, sc_charges.lease_interval_id, sc_charges.property_id, sc_charges.charge_amount, sc_charges.ar_origin_id, sc_charges.ar_code_type_id, sc_charges.scheduled_charge_type_id, sc_charges.charge_start_date, sc_charges.charge_end_date, sc_charges.ar_trigger_id, sc_charges.ar_code_id, current_manual_concession.one_time_concession
43. 25.909 235.174 ↓ 5.0 5 1,993

Sort (cost=15.17..15.17 rows=1 width=81) (actual time=0.118..0.118 rows=5 loops=1,993)

  • Sort Key: sc_charges.property_id, sc_charges.charge_amount, sc_charges.ar_origin_id, sc_charges.ar_code_type_id, sc_charges.scheduled_charge_type_id, sc_charges.charge_start_date, sc_charges.charge_end_date, sc_charges.ar_trigger_id, sc_charges.ar_code_id, current_manual_concession.one_time_concession
  • Sort Method: quicksort Memory: 25kB
44. 27.178 209.265 ↓ 5.0 5 1,993

Nested Loop Left Join (cost=9.43..15.16 rows=1 width=81) (actual time=0.059..0.105 rows=5 loops=1,993)

45. 61.783 115.594 ↓ 5.0 5 1,993

Bitmap Heap Scan on scheduled_charges sc_charges (cost=9.43..13.45 rows=1 width=49) (actual time=0.047..0.058 rows=5 loops=1,993)

  • Recheck Cond: ((cid = 8132) AND (lease_id = l.lease_id) AND (lease_interval_id = li.id))
  • Filter: ((NOT is_unselected_quote) AND (deleted_on IS NULL) AND (deleted_by IS NULL))
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=13297
46. 5.979 53.811 ↓ 0.0 0 1,993

BitmapAnd (cost=9.43..9.43 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=1,993)

47. 29.895 29.895 ↓ 10.2 51 1,993

Bitmap Index Scan on idx_scheduled_charges_remote_primary_key (cost=0.00..4.48 rows=5 width=0) (actual time=0.015..0.015 rows=51 loops=1,993)

  • Index Cond: ((cid = 8132) AND (lease_id = l.lease_id))
48. 17.937 17.937 ↑ 2.4 15 1,993

Bitmap Index Scan on idx_scheduled_charges_lease_interval_id (cost=0.00..4.70 rows=36 width=0) (actual time=0.009..0.009 rows=15 loops=1,993)

  • Index Cond: (lease_interval_id = li.id)
49. 66.493 66.493 ↓ 0.0 0 9,499

Seq Scan on ar_transaction_concessions current_manual_concession (cost=0.00..1.70 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=9,499)

  • Filter: ((cid = l.cid) AND (lease_id = l.lease_id) AND (lease_interval_id = li.id))
  • Rows Removed by Filter: 40
50. 5.979 5.979 ↑ 1.0 1 1,993

Index Scan using pk_lease_interval_types on lease_interval_types lit (cost=0.13..0.42 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,993)

  • Index Cond: (id = l.lease_interval_type_id)
51. 7.972 7.972 ↑ 1.0 1 1,993

Index Scan using idx_property_floorplans on property_floorplans pf (cost=0.28..4.76 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=1,993)

  • Index Cond: (id = l.property_floorplan_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
52. 7.972 7.972 ↑ 1.0 1 1,993

Index Scan using idx_unit_types_id on unit_types ut (cost=0.28..5.21 rows=1 width=246) (actual time=0.004..0.004 rows=1 loops=1,993)

  • Index Cond: (id = l.unit_type_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
53. 9.965 9.965 ↑ 1.0 1 1,993

Index Scan using pk_property_units on property_units pu (cost=0.29..8.04 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1,993)

  • Index Cond: ((cid = l.cid) AND (cid = 8132) AND (id = l.property_unit_id))
54. 17.937 17.937 ↑ 1.0 1 1,993

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_applications (cost=0.42..8.45 rows=1 width=27) (actual time=0.009..0.009 rows=1 loops=1,993)

  • Index Cond: ((cid = l.cid) AND (cid = 8132) AND (property_id = l.property_id) AND ((key)::text = 'CHANGE_UNIT_STATUS_TO_RENTED'::text))
55. 13.951 13.951 ↑ 1.0 1 1,993

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_transfers (cost=0.42..8.45 rows=1 width=27) (actual time=0.007..0.007 rows=1 loops=1,993)

  • Index Cond: ((cid = l.cid) AND (cid = 8132) AND (property_id = l.property_id) AND ((key)::text = 'TRANSFER_CHANGE_UNIT_STATUS_TO_RENTED'::text))