explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fa7Kf

Settings
# exclusive inclusive rows x rows loops node
1. 2.766 19,563.684 ↓ 1,173.0 1,173 1

Sort (cost=18,026.37..18,026.38 rows=1 width=1,504) (actual time=19,563.624..19,563.684 rows=1,173 loops=1)

  • Sort Key: inner_query.property_name, inner_query.building_name, inner_query.display_number
  • Sort Method: quicksort Memory: 630kB
2. 0.405 19,560.918 ↓ 1,173.0 1,173 1

Subquery Scan on inner_query (cost=18,025.66..18,026.36 rows=1 width=1,504) (actual time=19,555.133..19,560.918 rows=1,173 loops=1)

  • Filter: (inner_query.cid = 8,132)
3. 5.343 19,560.513 ↓ 1,173.0 1,173 1

WindowAgg (cost=18,025.66..18,026.35 rows=1 width=1,494) (actual time=19,555.130..19,560.513 rows=1,173 loops=1)

4. 4.263 19,552.253 ↓ 1,173.0 1,173 1

Sort (cost=16,362.84..16,362.85 rows=1 width=2,023) (actual time=19,552.116..19,552.253 rows=1,173 loops=1)

  • Sort Key: li.cid, (COALESCE(li.lease_id, cl.id)), li.id DESC
  • Sort Method: quicksort Memory: 636kB
5. 2.634 19,547.990 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=8,297.01..16,362.83 rows=1 width=2,023) (actual time=1,872.713..19,547.990 rows=1,173 loops=1)

6. 4.692 19,543.010 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=8,296.72..16,362.46 rows=1 width=2,007) (actual time=1,872.706..19,543.010 rows=1,173 loops=1)

7. 884.737 19,531.280 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=8,296.44..16,362.03 rows=1 width=1,993) (actual time=1,872.656..19,531.280 rows=1,173 loops=1)

  • Join Filter: ((prior_lease_info.cid = cl.cid) AND (prior_lease_info.property_id = cl.property_id) AND (prior_lease_info.lease_id = cl.id) AND (prior_lease_info.unit_space_id = cl.unit_space_id))
  • Rows Removed by Join Filter: 14,525,482
8. 2.462 2,791.102 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,990.85..13,056.39 rows=1 width=1,957) (actual time=155.354..2,791.102 rows=1,173 loops=1)

9. 1.538 2,714.741 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,933.21..12,998.72 rows=1 width=1,925) (actual time=155.200..2,714.741 rows=1,173 loops=1)

10. 4.467 2,689.743 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,932.64..12,997.37 rows=1 width=993) (actual time=155.164..2,689.743 rows=1,173 loops=1)

  • Join Filter: (pu.cid = cl.cid)
11. 1.859 1,725.762 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,930.20..12,891.18 rows=1 width=973) (actual time=155.096..1,725.762 rows=1,173 loops=1)

12. 2.140 1,721.557 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,930.06..12,891.03 rows=1 width=968) (actual time=155.081..1,721.557 rows=1,173 loops=1)

13. 2.544 1,710.033 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,929.77..12,890.11 rows=1 width=948) (actual time=155.058..1,710.033 rows=1,173 loops=1)

14. 2.762 1,703.970 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,929.49..12,889.77 rows=1 width=945) (actual time=155.034..1,703.970 rows=1,173 loops=1)

15. 2.225 1,700.035 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,929.21..12,889.44 rows=1 width=940) (actual time=155.013..1,700.035 rows=1,173 loops=1)

  • Join Filter: ((ca.cid = cl.cid) AND (ca.lease_id = cl.id))
16. 2.572 1,686.080 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,928.78..12,885.20 rows=1 width=924) (actual time=154.982..1,686.080 rows=1,173 loops=1)

  • Join Filter: ((lsw.cid = lt.cid) AND (lsw.lease_term_id = lt.id))
17. 30.246 1,683.508 ↓ 1,173.0 1,173 1

Nested Loop Left Join (cost=4,928.49..12,884.85 rows=1 width=932) (actual time=154.977..1,683.508 rows=1,173 loops=1)

  • Join Filter: ((inner_query_1.cid = cl.cid) AND (inner_query_1.lease_id = cl.id))
  • Rows Removed by Join Filter: 124,624
  • Filter: (((cl.move_in_date >= '2020-09-01'::date) AND (cl.move_in_date < '2020-10-01 00:00:00'::timestamp without time zone)) OR ((li.lease_start_date >= '2020-09-01'::date) AND (li.lease_start_date < '2020-10-01 00:00:00'::timestamp without time zone)) OR ((cl.notice_date >= '2020-09-01'::date) AND (cl.notice_date < '2020-10-01 00:00:00'::timestamp without time zone)) OR ((cl.move_out_date >= '2020-09-01'::date) AND (cl.move_out_date < '2020-10-01 00:00:00'::timestamp without time zone)) OR ((inner_query_1.notice_cancelled_date >= '2020-09-01 00:00:00+00'::timestamp with time zone) AND (inner_query_1.notice_cancelled_date < '2020-10-01 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 23,753
18. 23.066 1,503.706 ↓ 1,187.0 24,926 1

Nested Loop Left Join (cost=3.11..7,938.75 rows=21 width=908) (actual time=0.272..1,503.706 rows=24,926 loops=1)

19. 416.718 1,207.807 ↓ 2,755.9 24,803 1

Nested Loop Left Join (cost=2.12..7,894.40 rows=9 width=850) (actual time=0.236..1,207.807 rows=24,803 loops=1)

  • Join Filter: ((cntr.code)::text = (ua.country_code)::text)
  • Rows Removed by Join Filter: 5,442,668
20. 1.929 518.256 ↓ 2,755.9 24,803 1

Nested Loop Left Join (cost=2.12..7,838.67 rows=9 width=841) (actual time=0.079..518.256 rows=24,803 loops=1)

21. 20.164 466.721 ↓ 2,755.9 24,803 1

Nested Loop Left Join (cost=1.83..7,832.29 rows=9 width=679) (actual time=0.067..466.721 rows=24,803 loops=1)

22. 27.930 347.345 ↓ 2,755.9 24,803 1

Nested Loop (cost=1.41..7,819.12 rows=9 width=664) (actual time=0.058..347.345 rows=24,803 loops=1)

23. 6.584 195.400 ↓ 302.5 24,803 1

Nested Loop (cost=0.98..7,486.91 rows=82 width=608) (actual time=0.042..195.400 rows=24,803 loops=1)

24. 10.117 64.801 ↓ 18.3 24,803 1

Nested Loop (cost=0.56..4,911.90 rows=1,352 width=584) (actual time=0.032..64.801 rows=24,803 loops=1)

25. 0.036 0.036 ↑ 1.0 23 1

Index Scan using idx_load_prop_property_id on load_prop (cost=0.14..15.48 rows=23 width=517) (actual time=0.005..0.036 rows=23 loops=1)

26. 54.648 54.648 ↓ 18.3 1,078 23

Index Scan using idx_cached_leases_cid_property_id on cached_leases cl (cost=0.42..212.30 rows=59 width=71) (actual time=0.015..2.376 rows=1,078 loops=23)

  • Index Cond: ((cid = 8,132) AND (property_id = load_prop.property_id))
  • Filter: (occupancy_type_id = ANY ('{100,6,2,1,12,9,4,10}'::integer[]))
27. 124.015 124.015 ↑ 1.0 1 24,803

Index Scan using idx_lease_processes_lease_id on lease_processes lp (cost=0.42..1.89 rows=1 width=28) (actual time=0.004..0.005 rows=1 loops=24,803)

  • Index Cond: (lease_id = cl.id)
  • Filter: ((customer_id IS NULL) AND (cid = 8,132))
  • Rows Removed by Filter: 0
28. 124.015 124.015 ↑ 1.0 1 24,803

Index Scan using pk_customers on customers c (cost=0.42..4.05 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=24,803)

  • Index Cond: ((cid = 8,132) AND (id = cl.primary_customer_id))
29. 99.212 99.212 ↑ 1.0 1 24,803

Index Scan using idx_customer_phone_numbers_cid_customer_id on customer_phone_numbers cpn (cost=0.42..1.45 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=24,803)

  • Index Cond: ((cid = c.cid) AND (cid = 8,132) AND (customer_id = c.id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND is_primary)
  • Rows Removed by Filter: 0
30. 49.606 49.606 ↑ 1.0 1 24,803

Index Scan using idx_unit_addresses_property_unit_id on unit_addresses ua (cost=0.29..0.70 rows=1 width=174) (actual time=0.001..0.002 rows=1 loops=24,803)

  • Index Cond: (property_unit_id = cl.property_unit_id)
  • Filter: ((cid = 8,132) AND (cid = cl.cid))
31. 272.724 272.833 ↑ 1.1 220 24,803

Materialize (cost=0.00..22.74 rows=249 width=15) (actual time=0.000..0.011 rows=220 loops=24,803)

32. 0.109 0.109 ↑ 1.0 249 1

Seq Scan on countries cntr (cost=0.00..21.49 rows=249 width=15) (actual time=0.008..0.109 rows=249 loops=1)

33. 12.285 272.833 ↑ 2.0 1 24,803

Nested Loop Left Join (cost=0.99..4.91 rows=2 width=62) (actual time=0.010..0.011 rows=1 loops=24,803)

  • Join Filter: ((pcs.cid = lt.cid) AND (pcs.lease_term_structure_id = lt.lease_term_structure_id))
  • Rows Removed by Join Filter: 0
34. 0.000 248.030 ↑ 2.0 1 24,803

Nested Loop Left Join (cost=0.71..4.28 rows=2 width=66) (actual time=0.009..0.010 rows=1 loops=24,803)

35. 223.227 223.227 ↑ 1.0 1 24,803

Index Scan using idx_lease_intervals_major_composite on lease_intervals li (cost=0.43..3.92 rows=1 width=36) (actual time=0.008..0.009 rows=1 loops=24,803)

  • Index Cond: ((cid = cl.cid) AND (cid = 8,132) AND (lease_id = cl.id))
  • Filter: ((lease_status_type_id <> ALL ('{1,2}'::integer[])) AND ((id = cl.active_lease_interval_id) OR ((lease_interval_type_id = 3) AND (((lease_start_date >= '2020-09-01'::date) AND (lease_start_date <= '2020-09-30'::date)) OR ((lease_end_date >= '2020-09-01'::date) AND (lease_end_date <= '2020-09-30'::date))))))
  • Rows Removed by Filter: 2
36. 25.036 25.036 ↑ 1.0 1 12,518

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..0.36 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=12,518)

  • Index Cond: ((cid = li.cid) AND (cid = 8,132) AND (id = li.lease_term_id))
37. 12.518 12.518 ↑ 1.0 1 12,518

Index Scan using idx_property_charge_settings_property_id on property_charge_settings pcs (cost=0.28..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=12,518)

  • Index Cond: (property_id = li.property_id)
  • Filter: (cid = 8,132)
38. 0.000 149.556 ↓ 5.0 5 24,926

Materialize (cost=4,925.38..4,945.21 rows=1 width=28) (actual time=0.006..0.006 rows=5 loops=24,926)

39. 0.021 152.668 ↓ 5.0 5 1

Subquery Scan on inner_query_1 (cost=4,925.38..4,945.21 rows=1 width=28) (actual time=145.364..152.668 rows=5 loops=1)

  • Filter: (inner_query_1.row_num = 1)
40. 0.026 142.777 ↓ 5.0 5 1

WindowAgg (cost=4,925.38..4,925.41 rows=1 width=28) (actual time=142.760..142.777 rows=5 loops=1)

41. 0.021 142.751 ↓ 5.0 5 1

Sort (cost=4,925.38..4,925.39 rows=1 width=16) (actual time=142.749..142.751 rows=5 loops=1)

  • Sort Key: e.lease_id, e.event_datetime DESC
  • Sort Method: quicksort Memory: 25kB
42. 0.058 142.730 ↓ 5.0 5 1

Nested Loop (cost=0.56..4,925.37 rows=1 width=16) (actual time=40.621..142.730 rows=5 loops=1)

43. 0.026 0.026 ↑ 1.0 23 1

Seq Scan on load_prop load_prop_1 (cost=0.00..4.23 rows=23 width=4) (actual time=0.002..0.026 rows=23 loops=1)

44. 142.646 142.646 ↓ 0.0 0 23

Index Scan using idx_events_cid_etid_pid_event_datetime on events e (cost=0.56..213.95 rows=1 width=20) (actual time=6.177..6.202 rows=0 loops=23)

  • Index Cond: ((cid = 8,132) AND (event_type_id = 21) AND (property_id = load_prop_1.property_id))
  • Filter: ((lease_id IS NOT NULL) AND (old_status_id = ANY ('{6,5}'::integer[])) AND (new_status_id = 4) AND ((created_on)::date >= '2020-09-01'::date) AND ((created_on)::date <= '2020-09-30'::date))
  • Rows Removed by Filter: 1,751
45.          

SubPlan (for Subquery Scan)

46. 0.015 9.870 ↑ 1.0 1 5

Aggregate (cost=19.78..19.79 rows=1 width=8) (actual time=1.974..1.974 rows=1 loops=5)

47. 0.045 9.855 ↓ 2.0 2 5

Nested Loop (cost=0.56..19.77 rows=1 width=8) (actual time=1.236..1.971 rows=2 loops=5)

  • Join Filter: (e_1.property_id = load_prop_5.property_id)
  • Rows Removed by Join Filter: 40
48. 9.765 9.765 ↓ 2.0 2 5

Index Scan using idx_events_cid_lease_id on events e_1 (cost=0.56..15.26 rows=1 width=12) (actual time=1.228..1.953 rows=2 loops=5)

  • Index Cond: ((cid = inner_query_1.cid) AND (lease_id = inner_query_1.lease_id))
  • Filter: ((event_datetime < inner_query_1.notice_cancelled_date) AND CASE WHEN ((old_status_id = ANY ('{4,6}'::integer[])) AND (new_status_id = 5)) THEN true ELSE NULL::boolean END)
  • Rows Removed by Filter: 565
49. 0.045 0.045 ↑ 1.0 23 9

Seq Scan on load_prop load_prop_5 (cost=0.00..4.23 rows=23 width=4) (actual time=0.002..0.005 rows=23 loops=9)

50. 0.000 0.000 ↓ 0.0 0 1,173

Index Scan using pk_lease_start_windows on lease_start_windows lsw (cost=0.29..0.34 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=1,173)

  • Index Cond: ((cid = 8,132) AND (id = li.lease_start_window_id))
  • Filter: (property_id = li.property_id)
51. 11.730 11.730 ↑ 1.0 1 1,173

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.43..4.22 rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=1,173)

  • Index Cond: (lease_interval_id = li.id)
  • Filter: ((cid = 8,132) AND (application_status_id = 4) AND (application_stage_id = 4) AND (lease_interval_type_id = ANY ('{1,3,5}'::integer[])))
52. 1.173 1.173 ↓ 0.0 0 1,173

Index Scan using pk_company_employees on company_employees ce (cost=0.28..0.33 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=1,173)

  • Index Cond: ((cid = ca.cid) AND (cid = 8,132) AND (id = ca.leasing_agent_id))
53. 3.519 3.519 ↑ 1.0 1 1,173

Index Scan using pk_lead_sources on lead_sources ls (cost=0.28..0.33 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=1,173)

  • Index Cond: ((cid = c.cid) AND (cid = 8,132) AND (id = COALESCE(c.lead_source_id, ca.converting_lead_source_id)))
54. 9.384 9.384 ↑ 1.0 1 1,173

Index Scan using pk_unit_spaces on unit_spaces us (cost=0.29..0.92 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=1,173)

  • Index Cond: ((cid = cl.cid) AND (cid = 8,132) AND (id = cl.unit_space_id))
  • Filter: ((deleted_on IS NULL) OR (deleted_on > '2020-09-30 00:00:00+00'::timestamp with time zone))
55. 2.346 2.346 ↑ 1.0 1 1,173

Index Scan using pk_occupancy_types on occupancy_types ot (cost=0.14..0.15 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1,173)

  • Index Cond: (id = cl.occupancy_type_id)
56. 413.372 959.514 ↑ 1.0 1 1,173

Hash Right Join (cost=2.44..106.17 rows=1 width=24) (actual time=0.390..0.818 rows=1 loops=1,173)

  • Hash Cond: (property_unit_details.property_unit_id = pu.id)
57. 534.412 534.412 ↑ 1.0 5,007 1,084

Seq Scan on property_unit_details (cost=0.00..90.59 rows=5,007 width=12) (actual time=0.004..0.493 rows=5,007 loops=1,084)

  • Filter: (cid = 8,132)
58. 2.346 11.730 ↑ 1.0 1 1,173

Hash (cost=2.43..2.43 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1,173)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 9.384 9.384 ↑ 1.0 1 1,173

Index Scan using pk_property_units on property_units pu (cost=0.29..2.43 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1,173)

  • Index Cond: ((cid = 8,132) AND (id = us.property_unit_id))
  • Filter: ((deleted_on IS NULL) OR (deleted_on > '2020-09-30 00:00:00+00'::timestamp with time zone))
60. 1.441 23.460 ↓ 0.0 0 1,173

Nested Loop Left Join (cost=0.57..1.34 rows=1 width=940) (actual time=0.018..0.020 rows=0 loops=1,173)

61. 21.114 21.114 ↓ 0.0 0 1,173

Index Scan using idx_customer_addresses_company_customer_id on customer_addresses cad (cost=0.42..1.18 rows=1 width=931) (actual time=0.016..0.018 rows=0 loops=1,173)

  • Index Cond: (customer_id = c.id)
  • Filter: ((deleted_on IS NULL) AND (cid = 8,132) AND (address_type_id = 1))
  • Rows Removed by Filter: 2
62. 0.905 0.905 ↑ 1.0 1 181

Index Scan using pk_countries on countries cntr_1 (cost=0.14..0.17 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=181)

  • Index Cond: ((code)::text = (cad.country_code)::text)
63. 1.173 73.899 ↓ 0.0 0 1,173

Limit (cost=57.64..57.64 rows=1 width=52) (actual time=0.063..0.063 rows=0 loops=1,173)

64. 3.519 72.726 ↓ 0.0 0 1,173

Sort (cost=57.64..57.64 rows=1 width=52) (actual time=0.062..0.062 rows=0 loops=1,173)

  • Sort Key: cl_prior.move_in_date DESC
  • Sort Method: quicksort Memory: 25kB
65. 3.519 69.207 ↑ 1.0 1 1,173

GroupAggregate (cost=57.60..57.63 rows=1 width=52) (actual time=0.059..0.059 rows=1 loops=1,173)

  • Group Key: cl_prior.cid, cl_prior.id, us_1.id
66. 2.346 65.688 ↑ 1.0 1 1,173

Sort (cost=57.60..57.60 rows=1 width=20) (actual time=0.056..0.056 rows=1 loops=1,173)

  • Sort Key: cl_prior.id
  • Sort Method: quicksort Memory: 25kB
67. 2.553 63.342 ↑ 1.0 1 1,173

Nested Loop (cost=10.44..57.59 rows=1 width=20) (actual time=0.040..0.054 rows=1 loops=1,173)

68. 2.835 57.477 ↑ 1.0 1 1,173

Nested Loop Left Join (cost=10.44..53.29 rows=1 width=20) (actual time=0.037..0.049 rows=1 loops=1,173)

69. 0.435 16.422 ↑ 1.0 1 1,173

Nested Loop (cost=0.71..39.52 rows=1 width=20) (actual time=0.011..0.014 rows=1 loops=1,173)

70. 14.076 14.076 ↑ 1.0 1 1,173

Index Scan using idx_cached_leases_unit_space_id on cached_leases cl_prior (cost=0.42..31.20 rows=1 width=20) (actual time=0.009..0.012 rows=1 loops=1,173)

  • Index Cond: (unit_space_id = cl.unit_space_id)
  • Filter: ((id <> cl.id) AND (move_in_date < cl.move_in_date) AND (occupancy_type_id <> 5) AND (lease_status_type_id <> ALL ('{2,1}'::integer[])) AND (lease_interval_type_id <> 2) AND (cid = 8,132))
  • Rows Removed by Filter: 3
71. 1.911 1.911 ↑ 1.0 1 637

Index Scan using pk_unit_spaces on unit_spaces us_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=637)

  • Index Cond: ((cid = 8,132) AND (id = cl.unit_space_id))
  • Filter: ((deleted_on IS NULL) OR ('2020-09-30 00:00:00+00'::timestamp with time zone < deleted_on))
72. 21.658 38.220 ↑ 1.0 1 637

Bitmap Heap Scan on scheduled_charges sc (cost=9.72..13.76 rows=1 width=16) (actual time=0.054..0.060 rows=1 loops=637)

  • Recheck Cond: ((cid = cl_prior.cid) AND (cid = 8,132) AND (lease_id = cl_prior.id) AND (lease_interval_id = cl_prior.active_lease_interval_id))
  • Filter: ((NOT is_unselected_quote) AND ((deleted_on IS NULL) OR (deleted_on > '2020-09-30 00:00:00+00'::timestamp with time zone)) AND (ar_code_type_id = 2) AND (ar_trigger_id = ANY ('{301,302,303,304,305,306,307,308,309,310,312}'::integer[])))
  • Rows Removed by Filter: 11
  • Heap Blocks: exact=3,433
73. 1.911 16.562 ↓ 0.0 0 637

BitmapAnd (cost=9.72..9.72 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=637)

74. 8.281 8.281 ↓ 3.8 30 637

Bitmap Index Scan on idx_scheduled_charges_remote_primary_key (cost=0.00..4.53 rows=8 width=0) (actual time=0.013..0.013 rows=30 loops=637)

  • Index Cond: ((cid = cl_prior.cid) AND (cid = 8,132) AND (lease_id = cl_prior.id))
75. 6.370 6.370 ↑ 5.7 12 637

Bitmap Index Scan on idx_scheduled_charges_lease_interval_id (cost=0.00..4.94 rows=68 width=0) (actual time=0.010..0.010 rows=12 loops=637)

  • Index Cond: (lease_interval_id = cl_prior.active_lease_interval_id)
76. 3.312 3.312 ↑ 1.0 1 828

Seq Scan on load_prop load_prop_2 (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.004 rows=1 loops=828)

  • Filter: (property_id = cl.property_id)
  • Rows Removed by Filter: 22
77. 1,806.420 15,855.441 ↓ 12,384.0 12,384 1,173

Subquery Scan on prior_lease_info (cost=3,305.59..3,305.63 rows=1 width=56) (actual time=1.455..13.517 rows=12,384 loops=1,173)

  • Filter: ((prior_lease_info.cid = 8,132) AND (prior_lease_info.row_number = 1))
  • Rows Removed by Filter: 7,014
78. 10,971.069 14,049.021 ↓ 19,398.0 19,398 1,173

WindowAgg (cost=3,305.59..3,305.61 rows=1 width=64) (actual time=1.455..11.977 rows=19,398 loops=1,173)

79. 1,379.180 3,077.952 ↓ 19,398.0 19,398 1,173

Sort (cost=3,305.59..3,305.60 rows=1 width=56) (actual time=1.453..2.624 rows=19,398 loops=1,173)

  • Sort Key: li_1.lease_id, (sum(sc_1.normalized_amount))
  • Sort Method: quicksort Memory: 2,284kB
80. 10.959 1,698.772 ↓ 19,398.0 19,398 1

GroupAggregate (cost=3,305.55..3,305.58 rows=1 width=56) (actual time=1,686.521..1,698.772 rows=19,398 loops=1)

  • Group Key: li_1.cid, li_1.property_id, li_1.lease_id, us_2.id, li_1.lease_start_date, li_1.lease_end_date
81. 20.437 1,687.813 ↓ 23,284.0 23,284 1

Sort (cost=3,305.55..3,305.55 rows=1 width=28) (actual time=1,686.508..1,687.813 rows=23,284 loops=1)

  • Sort Key: li_1.cid, li_1.property_id, li_1.lease_id, us_2.id, li_1.lease_start_date, li_1.lease_end_date
  • Sort Method: quicksort Memory: 2,588kB
82. 16.917 1,667.376 ↓ 23,284.0 23,284 1

Nested Loop Left Join (cost=3,293.55..3,305.54 rows=1 width=28) (actual time=820.610..1,667.376 rows=23,284 loops=1)

  • Join Filter: ((row_number() OVER (?)) = 1)
  • Rows Removed by Join Filter: 7,983
83. 12.574 835.743 ↓ 19,398.0 19,398 1

WindowAgg (cost=3,293.12..3,293.14 rows=1 width=36) (actual time=820.292..835.743 rows=19,398 loops=1)

84. 10.825 823.169 ↓ 19,398.0 19,398 1

Sort (cost=3,293.12..3,293.12 rows=1 width=28) (actual time=820.281..823.169 rows=19,398 loops=1)

  • Sort Key: us_2.id, li_1.lease_id, li_1.id DESC
  • Sort Method: quicksort Memory: 2,284kB
85. 2.205 812.344 ↓ 19,398.0 19,398 1

Nested Loop (cost=4.67..3,293.11 rows=1 width=28) (actual time=0.387..812.344 rows=19,398 loops=1)

  • Join Filter: (load_prop_3.property_id = li_1.property_id)
86. 7.947 673.595 ↓ 17,068.0 17,068 1

Nested Loop (cost=4.24..3,289.34 rows=1 width=28) (actual time=0.367..673.595 rows=17,068 loops=1)

  • Join Filter: (load_prop_3.property_id = cl_1.property_id)
87. 0.836 10.160 ↓ 18.9 5,121 1

Nested Loop (cost=0.42..1,159.81 rows=271 width=16) (actual time=0.033..10.160 rows=5,121 loops=1)

88. 0.032 0.032 ↑ 1.0 23 1

Seq Scan on load_prop load_prop_3 (cost=0.00..4.23 rows=23 width=4) (actual time=0.001..0.032 rows=23 loops=1)

89. 9.292 9.292 ↓ 18.6 223 23

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us_2 (cost=0.42..50.12 rows=12 width=12) (actual time=0.010..0.404 rows=223 loops=23)

  • Index Cond: ((cid = 8,132) AND (property_id = load_prop_3.property_id))
90. 46.089 655.488 ↓ 3.0 3 5,121

Bitmap Heap Scan on cached_leases cl_1 (cost=3.83..7.85 rows=1 width=16) (actual time=0.122..0.128 rows=3 loops=5,121)

  • Recheck Cond: ((unit_space_id = us_2.id) AND (cid = 8,132) AND (property_id = us_2.property_id))
  • Filter: (occupancy_type_id <> 5)
  • Heap Blocks: exact=16,663
91. 3.800 609.399 ↓ 0.0 0 5,121

BitmapAnd (cost=3.83..3.83 rows=1 width=0) (actual time=0.119..0.119 rows=0 loops=5,121)

92. 10.242 10.242 ↑ 2.3 3 5,121

Bitmap Index Scan on idx_cached_leases_unit_space_id (cost=0.00..1.49 rows=7 width=0) (actual time=0.002..0.002 rows=3 loops=5,121)

  • Index Cond: (unit_space_id = us_2.id)
93. 595.357 595.357 ↓ 29.3 1,874 5,003

Bitmap Index Scan on idx_cached_leases_cid_property_id (cost=0.00..2.08 rows=64 width=0) (actual time=0.119..0.119 rows=1,874 loops=5,003)

  • Index Cond: ((cid = 8,132) AND (property_id = us_2.property_id))
94. 136.544 136.544 ↑ 1.0 1 17,068

Index Scan using idx_lease_intervals_major_composite on lease_intervals li_1 (cost=0.43..3.76 rows=1 width=24) (actual time=0.005..0.008 rows=1 loops=17,068)

  • Index Cond: ((cid = 8,132) AND (lease_id = cl_1.id))
  • Filter: ((lease_interval_type_id <> ALL ('{2,4}'::integer[])) AND (lease_status_type_id <> ALL ('{1,2}'::integer[])) AND (cl_1.property_id = property_id))
  • Rows Removed by Filter: 2
95. 814.716 814.716 ↑ 1.0 1 19,398

Index Scan using idx_scheduled_charges_remote_primary_key on scheduled_charges sc_1 (cost=0.43..12.37 rows=1 width=16) (actual time=0.035..0.042 rows=1 loops=19,398)

  • Index Cond: ((cid = li_1.cid) AND (lease_id = li_1.lease_id))
  • Filter: ((NOT is_unselected_quote) AND ((deleted_on IS NULL) OR (deleted_on > '2020-09-30 00:00:00+00'::timestamp with time zone)) AND (ar_code_type_id = 2) AND (lease_interval_id = li_1.id) AND (ar_trigger_id = ANY ('{301,302,303,304,305,306,307,308,309,310,312}'::integer[])))
  • Rows Removed by Filter: 39
96. 7.038 7.038 ↑ 1.0 1 1,173

Index Scan using pk_unit_types on unit_types ut (cost=0.28..0.43 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=1,173)

  • Index Cond: ((cid = pu.cid) AND (cid = 8,132) AND (id = pu.unit_type_id))
97. 2.346 2.346 ↓ 0.0 0 1,173

Index Scan using pk_list_items on list_items lsi (cost=0.28..0.37 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1,173)

  • Index Cond: ((cid = lp.cid) AND (cid = 8,132) AND (id = lp.move_out_reason_list_item_id))
98.          

SubPlan (for WindowAgg)

99. 0.028 2.917 ↓ 4.5 81 1

Hash Join (cost=574.04..1,662.77 rows=18 width=4) (actual time=2.611..2.917 rows=81 loops=1)

  • Hash Cond: (cl_2.property_id = load_prop_4.property_id)
100. 0.317 2.874 ↑ 3.7 81 1

Bitmap Heap Scan on cached_leases cl_2 (cost=569.52..1,656.94 rows=302 width=8) (actual time=2.588..2.874 rows=81 loops=1)

  • Recheck Cond: ((transfer_lease_id IS NOT NULL) AND (cid = 8,132))
  • Filter: (lease_interval_type_id = ANY ('{1,2,3}'::integer[]))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=80
101. 0.211 2.557 ↓ 0.0 0 1

BitmapAnd (cost=569.52..569.52 rows=304 width=0) (actual time=2.557..2.557 rows=0 loops=1)

102. 0.669 0.669 ↓ 1.1 6,620 1

Bitmap Index Scan on idx_cached_leases_transfer_lease_id (cost=0.00..113.62 rows=6,026 width=0) (actual time=0.669..0.669 rows=6,620 loops=1)

  • Index Cond: (transfer_lease_id IS NOT NULL)
103. 1.677 1.677 ↓ 1.0 24,803 1

Bitmap Index Scan on pk_cached_leases (cost=0.00..455.50 rows=24,411 width=0) (actual time=1.677..1.677 rows=24,803 loops=1)

  • Index Cond: (cid = 8,132)
104. 0.004 0.015 ↑ 1.0 23 1

Hash (cost=4.23..4.23 rows=23 width=4) (actual time=0.015..0.015 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
105. 0.011 0.011 ↑ 1.0 23 1

Seq Scan on load_prop load_prop_4 (cost=0.00..4.23 rows=23 width=4) (actual time=0.005..0.011 rows=23 loops=1)

Planning time : 28.077 ms