explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aUoY

Settings
# exclusive inclusive rows x rows loops node
1. 4.414 14,834.153 ↓ 987.0 987 1

Sort (cost=1,080.37..1,080.38 rows=1 width=1,140) (actual time=14,834.016..14,834.153 rows=987 loops=1)

  • Sort Key: l.property_name, (COALESCE((pf.number_of_bedrooms)::text, 'Unknown'::text))
  • Sort Method: quicksort Memory: 533kB
2. 27.614 14,829.739 ↓ 987.0 987 1

Nested Loop Left Join (cost=325.47..1,080.36 rows=1 width=1,140) (actual time=213.552..14,829.739 rows=987 loops=1)

3. 2.373 14,796.203 ↓ 987.0 987 1

Nested Loop Left Join (cost=325.18..1,071.51 rows=1 width=1,074) (actual time=212.979..14,796.203 rows=987 loops=1)

4. 2.809 14,788.895 ↓ 987.0 987 1

Nested Loop Left Join (cost=324.90..1,066.41 rows=1 width=840) (actual time=212.935..14,788.895 rows=987 loops=1)

5. 2.997 14,781.151 ↓ 987.0 987 1

Nested Loop Left Join (cost=324.62..1,061.66 rows=1 width=835) (actual time=212.913..14,781.151 rows=987 loops=1)

6. 3.035 14,775.193 ↓ 987.0 987 1

Nested Loop Left Join (cost=324.48..1,061.16 rows=1 width=823) (actual time=212.897..14,775.193 rows=987 loops=1)

7. 2.734 14,551.070 ↓ 987.0 987 1

Nested Loop Left Join (cost=302.59..1,037.42 rows=1 width=689) (actual time=212.523..14,551.070 rows=987 loops=1)

8. 1.595 14,542.414 ↓ 987.0 987 1

Nested Loop Left Join (cost=302.31..1,029.54 rows=1 width=553) (actual time=212.499..14,542.414 rows=987 loops=1)

9. 282.997 14,530.949 ↓ 987.0 987 1

Nested Loop (cost=293.97..1,021.18 rows=1 width=549) (actual time=212.477..14,530.949 rows=987 loops=1)

  • Join Filter: (cll.unit_space_id = past_recent_lease.unit_space_id)
  • Rows Removed by Join Filter: 1203462
10. 3.498 139.242 ↓ 627.5 1,255 1

Nested Loop Left Join (cost=51.49..395.76 rows=2 width=489) (actual time=1.532..139.242 rows=1,255 loops=1)

11. 2.442 130.724 ↓ 1,255.0 1,255 1

Nested Loop Left Join (cost=51.21..395.44 rows=1 width=493) (actual time=1.514..130.724 rows=1,255 loops=1)

12. 2.182 116.987 ↓ 1,255.0 1,255 1

Nested Loop (cost=50.78..386.98 rows=1 width=473) (actual time=1.493..116.987 rows=1,255 loops=1)

13. 2.408 107.275 ↓ 1,255.0 1,255 1

Nested Loop (cost=50.49..385.26 rows=1 width=469) (actual time=1.452..107.275 rows=1,255 loops=1)

14. 2.368 93.455 ↓ 1,268.0 1,268 1

Nested Loop (cost=50.07..383.82 rows=1 width=469) (actual time=1.412..93.455 rows=1,268 loops=1)

15. 3.041 84.747 ↓ 1,268.0 1,268 1

Nested Loop (cost=49.79..383.51 rows=1 width=475) (actual time=1.389..84.747 rows=1,268 loops=1)

  • Join Filter: (l.lease_id = cll.lease_id)
16. 3.698 9.430 ↓ 1,268.0 1,268 1

Hash Join (cost=49.36..371.91 rows=1 width=422) (actual time=1.292..9.430 rows=1,268 loops=1)

  • Hash Cond: ((l.lease_id = cro.lease_id) AND (l.property_id = cro.property_id) AND (l.application_id = cro.application_id))
17. 4.702 4.702 ↓ 53.3 2,451 1

Seq Scan on leads l (cost=0.00..322.02 rows=46 width=406) (actual time=0.239..4.702 rows=2,451 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: 6830
18. 0.544 1.030 ↑ 1.0 1,312 1

Hash (cost=26.40..26.40 rows=1,312 width=20) (actual time=1.028..1.030 rows=1,312 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
19. 0.486 0.486 ↑ 1.0 1,312 1

Seq Scan on rented_on cro (cost=0.00..26.40 rows=1,312 width=20) (actual time=0.011..0.486 rows=1,312 loops=1)

  • Filter: (cid = 2101)
20. 72.276 72.276 ↑ 1.0 1 1,268

Index Scan using idx_cached_lease_logs_cid_lease_id_log_datetime_date on cached_lease_logs cll (cost=0.43..11.59 rows=1 width=53) (actual time=0.056..0.057 rows=1 loops=1,268)

  • Index Cond: ((cid = 2101) AND (lease_id = cro.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
21. 6.340 6.340 ↑ 1.0 1 1,268

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=1,268)

  • Index Cond: (property_id = cll.property_id)
  • Filter: (cid = 2101)
22. 11.412 11.412 ↑ 1.0 1 1,268

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

  • Index Cond: ((cid = 2101) AND (id = cll.lease_id))
  • Filter: (lease_status_type_id <> 2)
  • Rows Removed by Filter: 0
23. 7.530 7.530 ↑ 1.0 1 1,255

Index Scan using idx_temp_min_max_leads_application_id on min_max_leads cal_max (cost=0.29..1.71 rows=1 width=28) (actual time=0.005..0.006 rows=1 loops=1,255)

  • Index Cond: (application_id = cro.application_id)
  • Filter: ((cid = 2101) AND (cro.property_id = property_id))
24. 11.295 11.295 ↑ 1.0 1 1,255

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

  • Index Cond: (id = l.lease_interval_id)
  • Filter: ((cid = 2101) AND (cid = l.cid) AND (lease_id = l.lease_id))
  • Rows Removed by Filter: 0
25. 5.020 5.020 ↑ 1.0 1 1,255

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

  • Index Cond: ((cid = li.cid) AND (cid = 2101) AND (id = li.lease_term_id))
26. 1,330.300 14,108.710 ↓ 106.7 960 1,255

Subquery Scan on past_recent_lease (cost=242.48..312.60 rows=9 width=68) (actual time=5.086..11.242 rows=960 loops=1,255)

  • 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: 3223
27. 5,562.160 12,778.410 ↓ 2.4 4,183 1,255

WindowAgg (cost=242.48..286.31 rows=1,753 width=80) (actual time=5.085..10.182 rows=4,183 loops=1,255)

28. 4,280.805 7,216.250 ↓ 2.4 4,183 1,255

Sort (cost=242.48..246.86 rows=1,753 width=72) (actual time=5.081..5.750 rows=4,183 loops=1,255)

  • 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: 587kB
29. 2,935.445 2,935.445 ↓ 2.4 4,183 1,255

Seq Scan on available_cached_leases cl_1 (cost=0.00..148.03 rows=1,753 width=72) (actual time=0.030..2.339 rows=4,183 loops=1,255)

  • Filter: ((lease_interval_id <> l.lease_interval_id) AND (lease_start_date < li.lease_start_date) AND (cid = 2101))
  • Rows Removed by Filter: 1076
30. 1.974 9.870 ↓ 0.0 0 987

Limit (cost=8.33..8.34 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=987)

31. 2.961 7.896 ↓ 0.0 0 987

Sort (cost=8.33..8.34 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=987)

  • Sort Key: e.event_datetime
  • Sort Method: quicksort Memory: 25kB
32. 4.935 4.935 ↓ 0.0 0 987

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=987)

  • 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))))
33. 5.922 5.922 ↑ 1.0 1 987

Index Scan using idx_temp_cached_leases_scheduled_charges_lease_interval_id on cached_leases_scheduled_charges lsc (cost=0.29..7.87 rows=1 width=156) (actual time=0.006..0.006 rows=1 loops=987)

  • 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. 70.077 221.088 ↑ 1.0 1 987

GroupAggregate (cost=21.89..23.72 rows=1 width=176) (actual time=0.224..0.224 rows=1 loops=987)

  • Group Key: sc.one_time_concession
35. 7.896 151.011 ↓ 6.0 6 987

Sort (cost=21.89..21.90 rows=1 width=73) (actual time=0.151..0.153 rows=6 loops=987)

  • Sort Key: sc.one_time_concession
  • Sort Method: quicksort Memory: 25kB
36. 3.948 143.115 ↓ 6.0 6 987

Subquery Scan on sc (cost=21.84..21.88 rows=1 width=73) (actual time=0.132..0.145 rows=6 loops=987)

37. 11.844 139.167 ↓ 6.0 6 987

WindowAgg (cost=21.84..21.87 rows=1 width=121) (actual time=0.131..0.141 rows=6 loops=987)

38. 11.844 127.323 ↓ 6.0 6 987

Sort (cost=21.84..21.84 rows=1 width=81) (actual time=0.128..0.129 rows=6 loops=987)

  • 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. 5.922 115.479 ↓ 6.0 6 987

Group (cost=21.79..21.83 rows=1 width=81) (actual time=0.112..0.117 rows=6 loops=987)

  • 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
40. 15.792 109.557 ↓ 6.0 6 987

Sort (cost=21.79..21.80 rows=1 width=81) (actual time=0.110..0.111 rows=6 loops=987)

  • 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
41. 6.944 93.765 ↓ 6.0 6 987

Nested Loop Left Join (cost=9.73..21.78 rows=1 width=81) (actual time=0.065..0.095 rows=6 loops=987)

42. 37.506 74.025 ↓ 6.0 6 987

Bitmap Heap Scan on scheduled_charges sc_charges (cost=9.45..13.47 rows=1 width=49) (actual time=0.058..0.075 rows=6 loops=987)

  • 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: 13
  • Heap Blocks: exact=6973
43. 3.948 36.519 ↓ 0.0 0 987

BitmapAnd (cost=9.45..9.45 rows=1 width=0) (actual time=0.037..0.037 rows=0 loops=987)

44. 18.753 18.753 ↓ 5.6 39 987

Bitmap Index Scan on idx_scheduled_charges_remote_primary_key (cost=0.00..4.50 rows=7 width=0) (actual time=0.019..0.019 rows=39 loops=987)

  • Index Cond: ((cid = 2101) AND (lease_id = l.lease_id))
45. 13.818 13.818 ↑ 1.9 19 987

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=19 loops=987)

  • Index Cond: (lease_interval_id = li.id)
46. 12.796 12.796 ↓ 0.0 0 6,398

Index Scan using idx_temp_ar_transction_concessions_lease_interval_id on ar_transaction_concessions current_manual_concession (cost=0.28..8.30 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=6,398)

  • Index Cond: (lease_interval_id = li.id)
  • Filter: ((cid = l.cid) AND (lease_id = l.lease_id))
47. 2.961 2.961 ↑ 1.0 1 987

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

  • Index Cond: (id = l.lease_interval_type_id)
48. 4.935 4.935 ↑ 1.0 1 987

Index Scan using idx_property_floorplans on property_floorplans pf (cost=0.28..4.74 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=987)

  • Index Cond: (id = l.property_floorplan_id)
  • Filter: ((cid = 2101) AND (cid = l.cid))
49. 4.935 4.935 ↑ 1.0 1 987

Index Scan using idx_unit_types_id on unit_types ut (cost=0.28..5.09 rows=1 width=246) (actual time=0.005..0.005 rows=1 loops=987)

  • Index Cond: (id = l.unit_type_id)
  • Filter: ((cid = 2101) AND (cid = l.cid))
50. 5.922 5.922 ↑ 1.0 1 987

Index Scan using pk_property_units on property_units pu (cost=0.29..8.05 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=987)

  • Index Cond: ((cid = l.cid) AND (cid = 2101) AND (id = l.property_unit_id))
Planning time : 44.044 ms