explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lc9dR

Settings
# exclusive inclusive rows x rows loops node
1. 1.114 2,606.882 ↓ 4.0 4 1

Subquery Scan on newly_available (cost=90.87..90.95 rows=1 width=82) (actual time=2,598.076..2,606.882 rows=4 loops=1)

  • Filter: (newly_available.rank = 1)
  • Rows Removed by Filter: 19,731
2. 6.392 2,605.768 ↓ 19,735.0 19,735 1

WindowAgg (cost=90.87..90.91 rows=1 width=86) (actual time=2,598.073..2,605.768 rows=19,735 loops=1)

3. 13.535 2,599.376 ↓ 19,735.0 19,735 1

Sort (cost=90.87..90.87 rows=1 width=63) (actual time=2,598.064..2,599.376 rows=19,735 loops=1)

  • Sort Key: ror.unit_space_id, usl_prior.id DESC
  • Sort Method: quicksort Memory: 3,544kB
4. 12.751 2,585.841 ↓ 19,735.0 19,735 1

Nested Loop Left Join (cost=2.44..90.86 rows=1 width=63) (actual time=496.014..2,585.841 rows=19,735 loops=1)

  • Filter: (((rpr.process_datetime IS NULL) AND (rpr.id IS NULL)) OR ((rpr.process_datetime IS NOT NULL) AND (rpr.id IS NOT NULL) AND ((rpr.updated_on)::date < usl.reporting_post_date)) OR ((rpr.process_datetime IS NULL) AND (rpr.id IS NOT NULL) AND ((rpr.updated_on)::date > usl.reporting_post_date)))
  • Rows Removed by Filter: 29,790
5. 1.569 1,132.882 ↓ 8,183.0 8,183 1

Nested Loop (cost=2.15..34.78 rows=1 width=79) (actual time=202.695..1,132.882 rows=8,183 loops=1)

6. 3.867 1,114.947 ↓ 8,183.0 8,183 1

Nested Loop (cost=1.86..32.43 rows=1 width=79) (actual time=202.683..1,114.947 rows=8,183 loops=1)

7. 2.283 1,078.348 ↓ 8,183.0 8,183 1

Nested Loop (cost=1.58..30.07 rows=1 width=64) (actual time=202.654..1,078.348 rows=8,183 loops=1)

8. 6.887 1,059.699 ↓ 8,183.0 8,183 1

Nested Loop (cost=1.29..27.65 rows=1 width=51) (actual time=202.633..1,059.699 rows=8,183 loops=1)

  • Join Filter: (ror.unit_space_id = usl.unit_space_id)
9. 5.261 151.309 ↓ 33,389.0 33,389 1

Nested Loop (cost=0.85..25.16 rows=1 width=32) (actual time=0.034..151.309 rows=33,389 loops=1)

10. 0.242 0.242 ↓ 57.0 57 1

Index Scan using idx_revenue_optimal_rents_property_id on revenue_optimal_rents ror (cost=0.29..22.52 rows=1 width=16) (actual time=0.010..0.242 rows=57 loops=1)

  • Index Cond: (property_id = 33,477)
  • Filter: (cid = 2,695)
11. 145.806 145.806 ↓ 586.0 586 57

Index Scan using idx_unit_space_logs_cid_unit_space_id_reporting_post_month_appl on unit_space_logs usl_prior (cost=0.56..2.61 rows=1 width=16) (actual time=0.018..2.558 rows=586 loops=57)

  • Index Cond: ((cid = 2,695) AND (unit_space_id = ror.unit_space_id))
  • Filter: ((unit_space_status_type_id <> 2) AND (unit_space_status_type_id <> 4) AND (unit_space_status_type_id <> 9) AND (is_post_date_ignored = 0))
  • Rows Removed by Filter: 1,197
12. 901.503 901.503 ↓ 0.0 0 33,389

Index Scan using idx_unit_space_logs_cache_date on unit_space_logs usl (cost=0.43..2.45 rows=1 width=35) (actual time=0.027..0.027 rows=0 loops=33,389)

  • Index Cond: ((property_id = 33,477) AND (unit_space_id = usl_prior.unit_space_id) AND (reporting_post_date > usl_prior.reporting_post_date) AND (CURRENT_DATE >= reporting_post_date) AND (CURRENT_DATE <= apply_through_post_date))
  • Filter: (is_available AND (deleted_on IS NULL) AND ((occupancy_type_id <> 6) OR (occupancy_type_id IS NULL)) AND (cid = 2,695) AND (is_marketed = 1) AND (unit_exclusion_reason_type_id = 1) AND (id <> usl_prior.id))
  • Rows Removed by Filter: 1
13. 16.366 16.366 ↑ 1.0 1 8,183

Index Scan using idx_unit_types_id on unit_types ut (cost=0.29..2.33 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=8,183)

  • Index Cond: (id = usl.unit_type_id)
  • Filter: ((cid = 2,695) AND (is_occupiable = 1))
14. 32.732 32.732 ↑ 1.0 1 8,183

Index Scan using idx_properties_id on properties p (cost=0.29..2.33 rows=1 width=23) (actual time=0.002..0.004 rows=1 loops=8,183)

  • Index Cond: (id = 33,477)
  • Filter: (cid = 2,695)
15. 16.366 16.366 ↑ 1.0 1 8,183

Index Scan using idx_property_charge_settings_property_id on property_charge_settings pcs (cost=0.29..2.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=8,183)

  • Index Cond: (property_id = 33,477)
  • Filter: (cid = 2,695)
16. 1,440.208 1,440.208 ↓ 6.0 6 8,183

Index Scan using idx_revenue_post_rents_unit_space_id on revenue_post_rents rpr (cost=0.29..56.03 rows=1 width=32) (actual time=0.020..0.176 rows=6 loops=8,183)

  • Index Cond: (usl.unit_space_id = unit_space_id)
  • Filter: ((cid = 2,695) AND (property_id = 33,477) AND (is_renewal = 0) AND (is_override = 0) AND (usl.cid = cid) AND (usl.property_id = property_id) AND ((updated_on)::date >= usl.reporting_post_date) AND ((updated_on)::date <= CURRENT_DATE))
  • Rows Removed by Filter: 337
Planning time : 8.470 ms
Execution time : 2,607.350 ms