explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z5Ix

Settings
# exclusive inclusive rows x rows loops node
1. 10.758 20,276.835 ↓ 1,223.0 1,223 1

Sort (cost=1,927.49..1,927.50 rows=1 width=1,140) (actual time=20,276.667..20,276.835 rows=1,223 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: 679kB
2. 40.724 20,266.077 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=631.30..1,927.48 rows=1 width=1,140) (actual time=218.941..20,266.077 rows=1,223 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: 770
3. 6.053 20,211.402 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=630.88..1,918.09 rows=1 width=1,097) (actual time=18.557..20,211.402 rows=1,993 loops=1)

4. 5.389 20,185.419 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=630.46..1,909.42 rows=1 width=1,078) (actual time=18.531..20,185.419 rows=1,993 loops=1)

5. 3.851 20,168.072 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=630.17..1,901.37 rows=1 width=1,078) (actual time=18.514..20,168.072 rows=1,993 loops=1)

6. 4.183 20,154.256 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=629.89..1,896.15 rows=1 width=844) (actual time=18.481..20,154.256 rows=1,993 loops=1)

7. 5.700 20,140.108 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=629.61..1,891.38 rows=1 width=839) (actual time=18.466..20,140.108 rows=1,993 loops=1)

8. 4.928 20,128.429 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=629.47..1,890.86 rows=1 width=827) (actual time=18.454..20,128.429 rows=1,993 loops=1)

9. 4.697 18,273.997 ↓ 1,993.0 1,993 1

Nested Loop Left Join (cost=272.53..1,532.85 rows=1 width=693) (actual time=18.064..18,273.997 rows=1,993 loops=1)

10. 619.682 18,259.335 ↓ 1,993.0 1,993 1

Nested Loop (cost=272.25..1,524.53 rows=1 width=557) (actual time=18.049..18,259.335 rows=1,993 loops=1)

  • Join Filter: (cll.unit_space_id = past_recent_lease.unit_space_id)
  • Rows Removed by Join Filter: 2881658
11. 3.473 966.945 ↓ 719.3 2,158 1

Nested Loop Left Join (cost=80.81..782.21 rows=3 width=501) (actual time=0.488..966.945 rows=2,158 loops=1)

12. 3.901 946.208 ↓ 719.3 2,158 1

Nested Loop Left Join (cost=72.48..757.13 rows=3 width=493) (actual time=0.473..946.208 rows=2,158 loops=1)

13. 5.960 931.517 ↓ 2,158.0 2,158 1

Nested Loop Left Join (cost=72.19..756.81 rows=1 width=497) (actual time=0.460..931.517 rows=2,158 loops=1)

14. 4.149 908.293 ↓ 2,158.0 2,158 1

Nested Loop (cost=71.77..748.35 rows=1 width=477) (actual time=0.447..908.293 rows=2,158 loops=1)

15. 3.261 112.158 ↓ 2,158.0 2,158 1

Nested Loop (cost=1.13..677.68 rows=1 width=465) (actual time=0.406..112.158 rows=2,158 loops=1)

16. 4.264 98.107 ↓ 2,158.0 2,158 1

Nested Loop (cost=0.85..677.37 rows=1 width=471) (actual time=0.398..98.107 rows=2,158 loops=1)

  • Join Filter: (l.lease_id = cll.lease_id)
17. 4.858 29.103 ↓ 2,158.0 2,158 1

Nested Loop (cost=0.42..670.33 rows=1 width=426) (actual time=0.364..29.103 rows=2,158 loops=1)

18. 4.616 4.616 ↓ 49.6 2,181 1

Seq Scan on leads l (cost=0.00..298.75 rows=44 width=414) (actual time=0.340..4.616 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
19. 19.629 19.629 ↑ 1.0 1 2,181

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..8.44 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=2,181)

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

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

  • Index Cond: ((cid = 8132) AND (lease_id = cl.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
21. 10.790 10.790 ↑ 1.0 1 2,158

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.005 rows=1 loops=2,158)

  • Index Cond: (property_id = cll.property_id)
  • Filter: (cid = 8132)
22. 265.434 791.986 ↑ 1.0 1 2,158

Aggregate (cost=70.64..70.65 rows=1 width=80) (actual time=0.367..0.367 rows=1 loops=2,158)

23. 526.552 526.552 ↓ 67.0 67 2,158

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_inner (cost=0.56..70.63 rows=1 width=16) (actual time=0.016..0.244 rows=67 loops=2,158)

  • Index Cond: ((cid = 8132) AND (application_id = l.application_id))
  • Filter: ((post_date <= '2018-12-31'::date) AND (property_id = l.property_id))
  • Rows Removed by Filter: 14
24. 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
25. 10.790 10.790 ↑ 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.005..0.005 rows=1 loops=2,158)

  • Index Cond: ((cid = li.cid) AND (cid = 8132) AND (id = li.lease_term_id))
26. 2.158 17.264 ↓ 0.0 0 2,158

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

27. 6.474 15.106 ↓ 0.0 0 2,158

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

  • Sort Key: e.event_datetime
  • Sort Method: quicksort Memory: 25kB
28. 8.632 8.632 ↓ 0.0 0 2,158

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=2,158)

  • 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
29. 1,804.088 16,672.708 ↓ 190.9 1,336 2,158

Subquery Scan on past_recent_lease (cost=191.43..247.35 rows=7 width=68) (actual time=3.404..7.726 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,672.536 14,868.620 ↓ 2.0 2,771 2,158

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

31. 4,572.802 8,196.084 ↓ 2.0 2,771 2,158

Sort (cost=191.43..194.93 rows=1,398 width=72) (actual time=3.400..3.798 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,623.282 3,623.282 ↓ 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.679 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. 9.965 9.965 ↑ 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.005..0.005 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))
34. 93.671 1,849.504 ↑ 1.0 1 1,993

GroupAggregate (cost=356.94..357.99 rows=1 width=176) (actual time=0.928..0.928 rows=1 loops=1,993)

  • Group Key: sc.one_time_concession
35. 13.951 1,755.833 ↓ 4.0 4 1,993

Sort (cost=356.94..356.95 rows=1 width=73) (actual time=0.880..0.881 rows=4 loops=1,993)

  • Sort Key: sc.one_time_concession
  • Sort Method: quicksort Memory: 25kB
36. 5.979 1,741.882 ↓ 4.0 4 1,993

Subquery Scan on sc (cost=356.89..356.93 rows=1 width=73) (actual time=0.865..0.874 rows=4 loops=1,993)

37. 17.937 1,735.903 ↓ 4.0 4 1,993

WindowAgg (cost=356.89..356.92 rows=1 width=121) (actual time=0.864..0.871 rows=4 loops=1,993)

38. 17.937 1,717.966 ↓ 4.0 4 1,993

Sort (cost=356.89..356.89 rows=1 width=81) (actual time=0.862..0.862 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
39. 9.965 1,700.029 ↓ 4.0 4 1,993

Group (cost=356.84..356.88 rows=1 width=81) (actual time=0.849..0.853 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, (sum(ar.transaction_amount))
40. 27.902 1,690.064 ↓ 5.0 5 1,993

Sort (cost=356.84..356.84 rows=1 width=81) (actual time=0.847..0.848 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, (sum(ar.transaction_amount))
  • Sort Method: quicksort Memory: 25kB
41. 13.243 1,662.162 ↓ 5.0 5 1,993

Nested Loop Left Join (cost=9.86..356.83 rows=1 width=81) (actual time=0.291..0.834 rows=5 loops=1,993)

42. 61.783 119.580 ↓ 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.049..0.060 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
43. 5.979 57.797 ↓ 0.0 0 1,993

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

44. 31.888 31.888 ↓ 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.016..0.016 rows=51 loops=1,993)

  • Index Cond: ((cid = 8132) AND (lease_id = l.lease_id))
45. 19.930 19.930 ↑ 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.010..0.010 rows=15 loops=1,993)

  • Index Cond: (lease_interval_id = li.id)
46. 9.499 1,529.339 ↓ 0.0 0 9,499

GroupAggregate (cost=0.43..343.36 rows=1 width=44) (actual time=0.161..0.161 rows=0 loops=9,499)

  • Group Key: ar.cid, ar.lease_id, ar.lease_interval_id
47. 1,519.840 1,519.840 ↓ 0.0 0 9,499

Index Scan using idx_ar_transactions_lease_id on ar_transactions ar (cost=0.43..343.34 rows=1 width=17) (actual time=0.160..0.160 rows=0 loops=9,499)

  • 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 = 8132) 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 ('{96315,193924,193925,193926,171093,112672,146021,146020,113732,113733,170988,113729,193927,113718,146019,146016,145687,113731,146017,96322,146015,146018,171445,96318,96317,96313,96327,96310,96325,96328,96319,96320,113813,193227,114581,113735,96321,113664,113736,114938,171092,114585,113734,194511,194512,113730,113726,96316,193229,96314,96311,171094,113712,113432,113439,113419,113420,113421,113429,113435,113445,113440,113447,113442,113424,113425,113433,113444,113446,113428,113414,113415,113416,113431,113426,113437,113436,113430,113417,113418,113422,113423,113438,113448,113413,113449,113441,113427,113434,113443,145858,96326,110887,96312,96324,170937,145859,145861,145862,145860}'::integer[])))
  • Rows Removed by Filter: 106
48. 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)
49. 9.965 9.965 ↑ 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.005..0.005 rows=1 loops=1,993)

  • Index Cond: (id = l.property_floorplan_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
50. 9.965 9.965 ↑ 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.005..0.005 rows=1 loops=1,993)

  • Index Cond: (id = l.unit_type_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
51. 11.958 11.958 ↑ 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.006..0.006 rows=1 loops=1,993)

  • Index Cond: ((cid = l.cid) AND (cid = 8132) AND (id = l.property_unit_id))
52. 19.930 19.930 ↑ 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.010..0.010 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))
53. 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))
Planning time : 15.783 ms