explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 775s

Settings
# exclusive inclusive rows x rows loops node
1. 9.104 11,191.455 ↓ 1,223.0 1,223 1

Sort (cost=877.37..877.37 rows=1 width=1,140) (actual time=11,191.290..11,191.455 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. 29.399 11,182.351 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=223.05..877.36 rows=1 width=1,140) (actual time=8.839..11,182.351 rows=1,223 loops=1)

3. 2.784 11,146.837 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=222.76..868.51 rows=1 width=1,074) (actual time=8.741..11,146.837 rows=1,223 loops=1)

4. 3.031 11,139.161 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=222.48..863.29 rows=1 width=840) (actual time=8.718..11,139.161 rows=1,223 loops=1)

5. 2.947 11,131.238 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=222.20..858.52 rows=1 width=835) (actual time=8.706..11,131.238 rows=1,223 loops=1)

6. 2.219 11,124.622 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=222.06..858.00 rows=1 width=823) (actual time=8.696..11,124.622 rows=1,223 loops=1)

7. 2.170 10,885.141 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=206.79..841.66 rows=1 width=689) (actual time=8.411..10,885.141 rows=1,223 loops=1)

8. 1.838 10,876.856 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=206.51..833.34 rows=1 width=553) (actual time=8.396..10,876.856 rows=1,223 loops=1)

9. 365.446 10,865.234 ↓ 1,223.0 1,223 1

Nested Loop (cost=198.17..824.98 rows=1 width=549) (actual time=8.379..10,865.234 rows=1,223 loops=1)

  • Join Filter: (cll.unit_space_id = past_recent_lease.unit_space_id)
  • Rows Removed by Join Filter: 1695078
10. 2.883 95.727 ↓ 407.7 1,223 1

Nested Loop Left Join (cost=6.74..82.66 rows=3 width=489) (actual time=0.278..95.727 rows=1,223 loops=1)

11. 1.919 87.952 ↓ 1,223.0 1,223 1

Nested Loop Left Join (cost=6.45..82.34 rows=1 width=493) (actual time=0.266..87.952 rows=1,223 loops=1)

12. 2.998 76.249 ↓ 1,223.0 1,223 1

Nested Loop (cost=6.03..73.88 rows=1 width=473) (actual time=0.252..76.249 rows=1,223 loops=1)

13. 1.485 64.690 ↓ 1,223.0 1,223 1

Nested Loop (cost=5.60..71.62 rows=1 width=473) (actual time=0.235..64.690 rows=1,223 loops=1)

14. 2.385 57.090 ↓ 1,223.0 1,223 1

Nested Loop (cost=5.33..71.31 rows=1 width=479) (actual time=0.225..57.090 rows=1,223 loops=1)

  • Join Filter: ((xmax.property_id = l.property_id) AND (xmax.application_id = l.application_id))
  • Rows Removed by Join Filter: 434
15. 1.721 47.367 ↓ 1,223.0 1,223 1

Nested Loop (cost=5.04..70.94 rows=1 width=101) (actual time=0.206..47.367 rows=1,223 loops=1)

16. 2.062 10.179 ↓ 1,223.0 1,223 1

Nested Loop (cost=4.61..62.46 rows=1 width=48) (actual time=0.169..10.179 rows=1,223 loops=1)

17. 0.662 0.779 ↓ 203.8 1,223 1

Bitmap Heap Scan on xyz xmax (cost=4.32..12.54 rows=6 width=20) (actual time=0.143..0.779 rows=1,223 loops=1)

  • Recheck Cond: (cid = 8132)
  • Heap Blocks: exact=8
18. 0.117 0.117 ↓ 203.8 1,223 1

Bitmap Index Scan on idx_temp_xyz_cid (cost=0.00..4.32 rows=6 width=0) (actual time=0.117..0.117 rows=1,223 loops=1)

  • Index Cond: (cid = 8132)
19. 7.338 7.338 ↑ 1.0 1 1,223

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

  • Index Cond: (application_id = xmax.application_id)
  • Filter: ((cid = 8132) AND (xmax.property_id = property_id))
20. 35.467 35.467 ↑ 1.0 1 1,223

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.028..0.029 rows=1 loops=1,223)

  • Index Cond: ((cid = 8132) AND (lease_id = xmax.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: 12
21. 7.338 7.338 ↑ 1.0 1 1,223

Index Scan using idx_temp_leads_lease_id on leads l (cost=0.29..0.36 rows=1 width=406) (actual time=0.005..0.006 rows=1 loops=1,223)

  • Index Cond: (lease_id = cll.lease_id)
  • 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: 1
22. 6.115 6.115 ↑ 1.0 1 1,223

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,223)

  • Index Cond: (property_id = cll.property_id)
  • Filter: (cid = 8132)
23. 8.561 8.561 ↑ 1.0 1 1,223

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

  • Index Cond: ((cid = 8132) AND (id = cll.lease_id))
  • Filter: (lease_status_type_id <> 2)
24. 9.784 9.784 ↑ 1.0 1 1,223

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

  • 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. 4.892 4.892 ↑ 1.0 1 1,223

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

  • Index Cond: ((cid = li.cid) AND (cid = 8132) AND (id = li.lease_term_id))
26. 1,084.801 10,404.061 ↓ 198.1 1,387 1,223

Subquery Scan on past_recent_lease (cost=191.43..247.35 rows=7 width=68) (actual time=3.827..8.507 rows=1,387 loops=1,223)

  • 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: 1854
27. 4,068.921 9,319.260 ↓ 2.3 3,241 1,223

WindowAgg (cost=191.43..226.38 rows=1,398 width=80) (actual time=3.826..7.620 rows=3,241 loops=1,223)

28. 3,059.946 5,250.339 ↓ 2.3 3,241 1,223

Sort (cost=191.43..194.93 rows=1,398 width=72) (actual time=3.823..4.293 rows=3,241 loops=1,223)

  • 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
29. 2,190.393 2,190.393 ↓ 2.3 3,241 1,223

Seq Scan on available_cached_leases cl_1 (cost=0.00..118.40 rows=1,398 width=72) (actual time=0.004..1.791 rows=3,241 loops=1,223)

  • Filter: ((lease_interval_id <> l.lease_interval_id) AND (lease_start_date < li.lease_start_date) AND (cid = 8132))
  • Rows Removed by Filter: 953
30. 1.223 9.784 ↓ 0.0 0 1,223

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

31. 3.669 8.561 ↓ 0.0 0 1,223

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

  • Sort Key: e.event_datetime
  • Sort Method: quicksort Memory: 25kB
32. 4.892 4.892 ↓ 0.0 0 1,223

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,223)

  • 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))))
33. 6.115 6.115 ↑ 1.0 1 1,223

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

  • 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. 55.035 237.262 ↑ 1.0 1 1,223

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

  • Group Key: sc.one_time_concession
35. 8.561 182.227 ↓ 4.0 4 1,223

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

  • Sort Key: sc.one_time_concession
  • Sort Method: quicksort Memory: 25kB
36. 2.446 173.666 ↓ 4.0 4 1,223

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

37. 9.784 171.220 ↓ 4.0 4 1,223

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

38. 11.007 161.436 ↓ 4.0 4 1,223

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

  • 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. 4.892 150.429 ↓ 4.0 4 1,223

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

  • 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.899 145.537 ↓ 4.0 4 1,223

Sort (cost=15.17..15.17 rows=1 width=81) (actual time=0.118..0.119 rows=4 loops=1,223)

  • 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. 12.594 129.638 ↓ 4.0 4 1,223

Nested Loop Left Join (cost=9.43..15.16 rows=1 width=81) (actual time=0.063..0.106 rows=4 loops=1,223)

42. 40.359 73.380 ↓ 4.0 4 1,223

Bitmap Heap Scan on scheduled_charges sc_charges (cost=9.43..13.45 rows=1 width=49) (actual time=0.050..0.060 rows=4 loops=1,223)

  • 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: 14
  • Heap Blocks: exact=8607
43. 3.669 33.021 ↓ 0.0 0 1,223

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

44. 18.345 18.345 ↓ 9.6 48 1,223

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=48 loops=1,223)

  • Index Cond: ((cid = 8132) AND (lease_id = l.lease_id))
45. 11.007 11.007 ↑ 2.0 18 1,223

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=18 loops=1,223)

  • Index Cond: (lease_interval_id = li.id)
46. 43.664 43.664 ↓ 0.0 0 5,458

Seq Scan on ar_transaction_concessions current_manual_concession (cost=0.00..1.70 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=5,458)

  • Filter: ((cid = l.cid) AND (lease_id = l.lease_id) AND (lease_interval_id = li.id))
  • Rows Removed by Filter: 40
47. 3.669 3.669 ↑ 1.0 1 1,223

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,223)

  • Index Cond: (id = l.lease_interval_type_id)
48. 4.892 4.892 ↑ 1.0 1 1,223

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,223)

  • Index Cond: (id = l.property_floorplan_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
49. 4.892 4.892 ↑ 1.0 1 1,223

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,223)

  • Index Cond: (id = l.unit_type_id)
  • Filter: ((cid = 8132) AND (cid = l.cid))
50. 6.115 6.115 ↑ 1.0 1 1,223

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,223)

  • Index Cond: ((cid = l.cid) AND (cid = 8132) AND (id = l.property_unit_id))