explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M7G9

Settings
# exclusive inclusive rows x rows loops node
1. 21.816 217,337.854 ↓ 2,298.0 2,298 1

Sort (cost=3,445.72..3,445.72 rows=1 width=1,140) (actual time=217,337.536..217,337.854 rows=2,298 loops=1)

  • Sort Key: l.property_name, (CASE WHEN (3 = l.lease_interval_type_id) THEN ((min(cal_inner.lease_approved_on)))::date WHEN (1 = l.lease_interval_type_id) THEN CASE WHEN ('12'::text = pp_applications.value) THEN (l.application_approved_on)::date WHEN ('18'::text = pp_applications.value) THEN ((min(cal_inner.lease_approved_on)))::date ELSE (LEAST(l.application_completed, e.event_datetime))::date 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 WHEN ('52'::text = pp_transfers.value) THEN (l.application_approved_on)::date ELSE ((min(cal_inner.lease_approved_on)))::date END ELSE NULL::date END) 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: 1281kB
2. 124.862 217,316.038 ↓ 2,298.0 2,298 1

Nested Loop Left Join (cost=1,236.27..3,445.71 rows=1 width=1,140) (actual time=12,262.895..217,316.038 rows=2,298 loops=1)

  • Filter: CASE WHEN (3 = l.lease_interval_type_id) THEN ((((min(cal_inner.lease_approved_on)))::date >= '2018-01-01'::date) AND (((min(cal_inner.lease_approved_on)))::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 ((((min(cal_inner.lease_approved_on)))::date >= '2018-01-01'::date) AND (((min(cal_inner.lease_approved_on)))::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 ((((min(cal_inner.lease_approved_on)))::date >= '2018-01-01'::date) AND (((min(cal_inner.lease_approved_on)))::date < '2019-01-01 00:00:00'::timestamp without time zone)) END ELSE NULL::boolean END
  • Rows Removed by Filter: 3538
3. 19.461 217,144.488 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,235.85..3,436.57 rows=1 width=1,097) (actual time=472.356..217,144.488 rows=5,836 loops=1)

4. 22.826 217,049.159 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,235.43..3,428.21 rows=1 width=1,078) (actual time=472.316..217,049.159 rows=5,836 loops=1)

5. 19.371 216,979.645 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,235.14..3,420.78 rows=1 width=1,078) (actual time=472.297..216,979.645 rows=5,836 loops=1)

6. 19.653 216,919.422 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,234.85..3,417.31 rows=1 width=844) (actual time=472.231..216,919.422 rows=5,836 loops=1)

7. 22.647 216,858.917 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,234.57..3,414.16 rows=1 width=839) (actual time=472.196..216,858.917 rows=5,836 loops=1)

8. 26.856 216,812.926 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=1,234.44..3,413.88 rows=1 width=827) (actual time=472.174..216,812.926 rows=5,836 loops=1)

9. 24.050 206,222.910 ↓ 5,836.0 5,836 1

Nested Loop Left Join (cost=853.82..3,031.41 rows=1 width=693) (actual time=471.953..206,222.910 rows=5,836 loops=1)

10. 4,302.969 206,140.500 ↓ 5,836.0 5,836 1

Nested Loop (cost=853.53..3,023.86 rows=1 width=557) (actual time=471.924..206,140.500 rows=5,836 loops=1)

  • Join Filter: (cll.unit_space_id = past_recent_lease.unit_space_id)
  • Rows Removed by Join Filter: 20151366
11. 17.673 4,670.891 ↓ 6,710.0 6,710 1

Nested Loop Left Join (cost=104.09..2,068.57 rows=1 width=505) (actual time=1.806..4,670.891 rows=6,710 loops=1)

12. 25.834 4,579.408 ↓ 6,710.0 6,710 1

Nested Loop Left Join (cost=95.75..2,060.21 rows=1 width=497) (actual time=1.752..4,579.408 rows=6,710 loops=1)

13. 18.398 4,513.314 ↓ 6,710.0 6,710 1

Nested Loop (cost=95.47..2,059.90 rows=1 width=497) (actual time=1.734..4,513.314 rows=6,710 loops=1)

14. 25.083 683.636 ↓ 6,710.0 6,710 1

Nested Loop (cost=1.56..1,965.95 rows=1 width=485) (actual time=0.552..683.636 rows=6,710 loops=1)

  • Join Filter: (cll.lease_id = cl.id)
15. 19.240 590.183 ↓ 6,837.0 6,837 1

Nested Loop (cost=1.14..1,965.01 rows=1 width=485) (actual time=0.507..590.183 rows=6,837 loops=1)

16. 17.687 523.084 ↓ 6,837.0 6,837 1

Nested Loop (cost=0.86..1,964.70 rows=1 width=491) (actual time=0.485..523.084 rows=6,837 loops=1)

  • Join Filter: (l.lease_id = cll.lease_id)
17. 22.607 129.362 ↓ 6,837.0 6,837 1

Nested Loop (cost=0.43..1,960.50 rows=1 width=438) (actual time=0.355..129.362 rows=6,837 loops=1)

18. 24.327 24.327 ↓ 54.1 6,869 1

Seq Scan on leads l (cost=0.00..894.08 rows=127 width=414) (actual time=0.336..24.327 rows=6,869 loops=1)

  • Filter: ((unit_number_cache IS NOT NULL) AND (post_date >= '2018-01-01'::date) AND (cid = 2101) AND (lease_interval_type_id = ANY ('{1,5,3}'::integer[])))
  • Rows Removed by Filter: 18682
19. 82.428 82.428 ↑ 1.0 1 6,869

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.43..8.39 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=6,869)

  • Index Cond: (id = l.lease_interval_id)
  • Filter: ((cid = 2101) AND (l.lease_id = lease_id))
  • Rows Removed by Filter: 0
20. 376.035 376.035 ↑ 1.0 1 6,837

Index Scan using idx_cached_lease_logs_cid_lease_id_log_datetime_date on cached_lease_logs cll (cost=0.43..4.19 rows=1 width=53) (actual time=0.054..0.055 rows=1 loops=6,837)

  • Index Cond: ((cid = 2101) AND (lease_id = li.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: 17
21. 47.859 47.859 ↑ 1.0 1 6,837

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.006..0.007 rows=1 loops=6,837)

  • Index Cond: (property_id = cll.property_id)
  • Filter: (cid = 2101)
22. 68.370 68.370 ↑ 1.0 1 6,837

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..0.93 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=6,837)

  • Index Cond: ((cid = 2101) AND (id = li.lease_id))
  • Filter: (lease_status_type_id <> 2)
  • Rows Removed by Filter: 0
23. 1,053.470 3,811.280 ↑ 1.0 1 6,710

Aggregate (cost=93.91..93.92 rows=1 width=80) (actual time=0.568..0.568 rows=1 loops=6,710)

24. 2,757.810 2,757.810 ↓ 75.0 75 6,710

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_inner (cost=0.56..93.91 rows=1 width=16) (actual time=0.027..0.411 rows=75 loops=6,710)

  • Index Cond: ((cid = 2101) AND (application_id = l.application_id))
  • Filter: ((post_date <= '2018-12-31'::date) AND (property_id = l.property_id))
  • Rows Removed by Filter: 9
25. 40.260 40.260 ↑ 1.0 1 6,710

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..0.31 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=6,710)

  • Index Cond: ((cid = li.cid) AND (cid = 2101) AND (id = li.lease_term_id))
26. 6.710 73.810 ↓ 0.0 0 6,710

Limit (cost=8.33..8.34 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=6,710)

27. 33.550 67.100 ↓ 0.0 0 6,710

Sort (cost=8.33..8.34 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=6,710)

  • Sort Key: e.event_datetime
  • Sort Method: quicksort Memory: 25kB
28. 33.550 33.550 ↓ 0.0 0 6,710

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.005..0.005 rows=0 loops=6,710)

  • Index Cond: (lease_interval_id = l.lease_interval_id)
  • Filter: ((cid = 2101) 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
29. 18,573.280 197,166.640 ↓ 115.5 3,004 6,710

Subquery Scan on past_recent_lease (cost=749.44..954.96 rows=26 width=68) (actual time=13.935..29.384 rows=3,004 loops=6,710)

  • Filter: ((past_recent_lease.lease_id IS NOT NULL) AND (past_recent_lease.cid = 2101) AND (past_recent_lease.prior_lease_row_num = 1))
  • Rows Removed by Filter: 7613
30. 73,675.800 178,593.360 ↓ 2.1 10,617 6,710

WindowAgg (cost=749.44..877.89 rows=5,138 width=80) (actual time=13.933..26.616 rows=10,617 loops=6,710)

31. 59,953.850 104,917.560 ↓ 2.1 10,617 6,710

Sort (cost=749.44..762.29 rows=5,138 width=72) (actual time=13.927..15.636 rows=10,617 loops=6,710)

  • 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: 1957kB
32. 44,963.710 44,963.710 ↓ 2.1 10,617 6,710

Seq Scan on available_cached_leases cl_1 (cost=0.00..432.76 rows=5,138 width=72) (actual time=0.068..6.701 rows=10,617 loops=6,710)

  • Filter: ((lease_interval_id <> l.lease_interval_id) AND (lease_start_date < li.lease_start_date) AND (cid = 2101))
  • Rows Removed by Filter: 4798
33. 58.360 58.360 ↑ 1.0 1 5,836

Index Scan using idx_temp_cached_leases_scheduled_charges_lease_interval_id on cached_leases_scheduled_charges lsc (cost=0.29..7.54 rows=1 width=156) (actual time=0.010..0.010 rows=1 loops=5,836)

  • Index Cond: (lease_interval_id = past_recent_lease.lease_interval_id)
  • Filter: ((cid = 2101) AND (cid = past_recent_lease.cid) AND (lease_id = past_recent_lease.lease_id))
34. 426.028 10,563.160 ↑ 1.0 1 5,836

GroupAggregate (cost=380.62..382.45 rows=1 width=176) (actual time=1.810..1.810 rows=1 loops=5,836)

  • Group Key: sc.one_time_concession
35. 58.360 10,137.132 ↓ 5.0 5 5,836

Sort (cost=380.62..380.63 rows=1 width=73) (actual time=1.736..1.737 rows=5 loops=5,836)

  • Sort Key: sc.one_time_concession
  • Sort Method: quicksort Memory: 25kB
36. 23.344 10,078.772 ↓ 5.0 5 5,836

Subquery Scan on sc (cost=380.57..380.61 rows=1 width=73) (actual time=1.716..1.727 rows=5 loops=5,836)

37. 64.196 10,055.428 ↓ 5.0 5 5,836

WindowAgg (cost=380.57..380.60 rows=1 width=121) (actual time=1.714..1.723 rows=5 loops=5,836)

38. 70.032 9,991.232 ↓ 5.0 5 5,836

Sort (cost=380.57..380.57 rows=1 width=81) (actual time=1.711..1.712 rows=5 loops=5,836)

  • 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
39. 35.016 9,921.200 ↓ 5.0 5 5,836

Group (cost=380.52..380.56 rows=1 width=81) (actual time=1.695..1.700 rows=5 loops=5,836)

  • 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, (sum(ar.transaction_amount))
40. 93.376 9,886.184 ↓ 5.0 5 5,836

Sort (cost=380.52..380.52 rows=1 width=81) (actual time=1.693..1.694 rows=5 loops=5,836)

  • 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, (sum(ar.transaction_amount))
  • Sort Method: quicksort Memory: 25kB
41. 62.190 9,792.808 ↓ 5.0 5 5,836

Nested Loop Left Join (cost=9.88..380.51 rows=1 width=81) (actual time=0.471..1.678 rows=5 loops=5,836)

42. 186.752 408.520 ↓ 5.0 5 5,836

Bitmap Heap Scan on scheduled_charges sc_charges (cost=9.45..13.47 rows=1 width=49) (actual time=0.055..0.070 rows=5 loops=5,836)

  • Recheck Cond: ((cid = 2101) 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: 9
  • Heap Blocks: exact=33441
43. 23.358 221.768 ↓ 0.0 0 5,836

BitmapAnd (cost=9.45..9.45 rows=1 width=0) (actual time=0.038..0.038 rows=0 loops=5,836)

44. 116.720 116.720 ↓ 6.4 45 5,836

Bitmap Index Scan on idx_scheduled_charges_remote_primary_key (cost=0.00..4.50 rows=7 width=0) (actual time=0.020..0.020 rows=45 loops=5,836)

  • Index Cond: ((cid = 2101) AND (lease_id = l.lease_id))
45. 81.690 81.690 ↑ 2.6 14 5,835

Bitmap Index Scan on idx_scheduled_charges_lease_interval_id (cost=0.00..4.70 rows=36 width=0) (actual time=0.014..0.014 rows=14 loops=5,835)

  • Index Cond: (lease_interval_id = li.id)
46. 153.830 9,322.098 ↓ 0.0 0 30,766

GroupAggregate (cost=0.43..367.02 rows=1 width=44) (actual time=0.303..0.303 rows=0 loops=30,766)

  • Group Key: ar.cid, ar.lease_id, ar.lease_interval_id
47. 9,168.268 9,168.268 ↓ 6.0 6 30,766

Index Scan using idx_ar_transactions_lease_id on ar_transactions ar (cost=0.43..367.00 rows=1 width=17) (actual time=0.254..0.298 rows=6 loops=30,766)

  • Index Cond: (lease_id = l.lease_id)
  • Filter: ((scheduled_charge_id IS NULL) AND (NOT is_deleted) AND (NOT is_reversal) AND (transaction_amount < '0'::numeric) AND (cid = 2101) AND (lease_interval_id = li.id) AND (ar_trigger_id = ANY ('{201,202,204,205,206,207,208,209,210,211,212}'::integer[])) AND (ar_code_id = ANY ('{32842,82624,134674,36389,82664,82665,147329,146927,82673,143082,90355,144257,82591,36335,104346,82592,146792,143046,32854,194090,36019,83967,143033,82593,82594,82595,32843,83647,83646,194509,129449,82596,82597,82598,82599,144258,144259,82600,82601,82602,82603,82605,137217,82606,135698,147337,143049,82607,63238,82608,63211,82609,63216,82610,147558,82611,143047,143072,82642,147305,82612,143048,82613,82614,82615,82616,82617,63242,63214,194083,147307,63231,82618,143045,147301,136176,63210,82619,147299,194086,147308,82620,82604,147412,93862,147310,194088,147302,143059,143044,82621,82622,194089,115852,147309,194087,143073,82623,143087,143051,147304,82625,82626,86151,135555,82627,82628,143055,82629,143056,82630,146445,82631,82632,82634,143058,127404,82635,147485,193888,138813,143060,137252,82636,82637,143063,82638,143064,143031,205564,143043,205565,127554,143065,141458,141459,143042,93863,194022,143066,82639,82640,143067,143068,134786,143080,143035,144849,82641,194084,63251,111259,143069,147559,82643,32850,143041,82644,143070,82645,147326,82646,143036,147300,143077,143078,82647,82648,82649,143074,82650,82651,82652,82653,82654,82655,82656,82657,82658,82659,143053,143037,146449,146450,146843,82660,146451,146452,146454,82661,143038,143081,147483,143054,147311,63232,79913,147306,82662,82663,82666,82667,82668,109071,146427,147328,143086,147334,118513,118514,205913,36029,194091,194085,82670,143085,82671,143039,143083,143084,82672,90356,105248,147303,86152,143088,143089,143090,82674,82675,147324,147314,147321,147323,147344,143052,147345,143057,147312,147313,194530,147342,147341,147322,147317,147319,147316,147318,147315,143079,147320,194094,194096,194097,194098,194095,147325,147343,147340,194151,147338,147339,194508,194507,147288,147284,194531,205572,205828,147290,147287,147293,147292,194514,194150,147289,147285,147291,205569,147286,205686,147283,205617,145870,147298,32844,147330,193782,127555,147295,82676,147294,82677,32845,32855,194438,194379,82633,147296,145871,141718,147297,194513,141721,82669,145873,145874,145872}'::integer[])))
  • Rows Removed by Filter: 209
48. 23.344 23.344 ↑ 1.0 1 5,836

Index Scan using pk_lease_interval_types on lease_interval_types lit (cost=0.13..0.24 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=5,836)

  • Index Cond: (id = l.lease_interval_type_id)
49. 40.852 40.852 ↑ 1.0 1 5,836

Index Scan using idx_property_floorplans on property_floorplans pf (cost=0.28..3.14 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=5,836)

  • Index Cond: (id = l.property_floorplan_id)
  • Filter: ((cid = 2101) AND (cid = l.cid))
50. 40.852 40.852 ↑ 1.0 1 5,836

Index Scan using idx_unit_types_id on unit_types ut (cost=0.28..3.45 rows=1 width=246) (actual time=0.007..0.007 rows=1 loops=5,836)

  • Index Cond: (id = l.unit_type_id)
  • Filter: ((cid = 2101) AND (cid = l.cid))
51. 46.688 46.688 ↑ 1.0 1 5,836

Index Scan using pk_property_units on property_units pu (cost=0.29..7.43 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=5,836)

  • Index Cond: ((cid = l.cid) AND (cid = 2101) AND (id = l.property_unit_id))
52. 75.868 75.868 ↑ 1.0 1 5,836

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_applications (cost=0.42..8.19 rows=1 width=27) (actual time=0.013..0.013 rows=1 loops=5,836)

  • Index Cond: ((cid = l.cid) AND (cid = 2101) AND (property_id = l.property_id) AND ((key)::text = 'CHANGE_UNIT_STATUS_TO_RENTED'::text))
53. 46.688 46.688 ↑ 1.0 1 5,836

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp_transfers (cost=0.42..8.19 rows=1 width=27) (actual time=0.008..0.008 rows=1 loops=5,836)

  • Index Cond: ((cid = l.cid) AND (cid = 2101) AND (property_id = l.property_id) AND ((key)::text = 'TRANSFER_CHANGE_UNIT_STATUS_TO_RENTED'::text))
Planning time : 39.539 ms