explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hIAW

Settings
# exclusive inclusive rows x rows loops node
1. 0.413 421.235 ↓ 3.0 3 1

Group (cost=517.30..517.32 rows=1 width=249) (actual time=420.730..421.235 rows=3 loops=1)

  • Group Key: lsw.start_date, lsw.end_date, lt.id, lt.cid, lsw.id, lsw.billing_end_date, lsw.property_id
2. 1.905 420.822 ↓ 2,133.0 2,133 1

Sort (cost=517.30..517.30 rows=1 width=249) (actual time=420.724..420.822 rows=2,133 loops=1)

  • Sort Key: lsw.start_date, lsw.end_date, lt.id, lsw.id, lsw.billing_end_date, lsw.property_id
  • Sort Method: quicksort Memory: 1,196kB
3. 1.345 418.917 ↓ 2,133.0 2,133 1

Nested Loop (cost=462.06..517.29 rows=1 width=249) (actual time=125.519..418.917 rows=2,133 loops=1)

  • Join Filter: ((p.id = pcs.property_id) AND (lt.lease_term_structure_id = pcs.lease_term_structure_id))
4. 1.755 415.439 ↓ 2,133.0 2,133 1

Nested Loop (cost=461.79..516.98 rows=1 width=261) (actual time=125.506..415.439 rows=2,133 loops=1)

  • Join Filter: (lsw.lease_term_id = lt.id)
5. 0.413 411.551 ↓ 2,133.0 2,133 1

Nested Loop (cost=461.51..516.66 rows=1 width=48) (actual time=125.475..411.551 rows=2,133 loops=1)

  • Join Filter: (p.id = cr.property_id)
6. 0.007 1.658 ↓ 4.0 4 1

Nested Loop (cost=0.81..3.05 rows=1 width=36) (actual time=1.627..1.658 rows=4 loops=1)

7. 0.003 1.615 ↑ 1.0 1 1

Nested Loop (cost=0.53..2.27 rows=1 width=12) (actual time=1.613..1.615 rows=1 loops=1)

8. 1.602 1.602 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=4) (actual time=1.601..1.602 rows=1 loops=1)

  • Filter: (is_disabled = 0)
9. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using idx_properties_cid_id_property_name on properties p (cost=0.28..1.40 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((cid = 3,395) AND (id = lp.property_id))
  • Heap Fetches: 1
10. 0.036 0.036 ↓ 4.0 4 1

Index Scan using idx_lease_start_windows_property_id on lease_start_windows lsw (cost=0.28..0.77 rows=1 width=28) (actual time=0.012..0.036 rows=4 loops=1)

  • Index Cond: (property_id = p.id)
  • Filter: ((deleted_on IS NULL) AND is_active AND (cid = 3,395) AND (end_date >= CURRENT_DATE))
  • Rows Removed by Filter: 1
11. 10.008 409.480 ↓ 533.0 533 4

Bitmap Heap Scan on cached_rates cr (cost=460.70..513.60 rows=1 width=16) (actual time=100.473..102.370 rows=533 loops=4)

  • Recheck Cond: ((property_id = lsw.property_id) AND (lease_term_id = lsw.lease_term_id))
  • Filter: ((cid = 3,395) AND (lsw.id = lease_start_window_id) AND ((effective_date = CURRENT_DATE) OR (effective_date IS NULL)))
  • Rows Removed by Filter: 5,688
  • Heap Blocks: exact=1,115
12. 2.428 399.472 ↓ 0.0 0 4

BitmapAnd (cost=460.70..460.70 rows=51 width=0) (actual time=99.868..99.868 rows=0 loops=4)

13. 2.136 2.136 ↓ 1.0 10,665 4

Bitmap Index Scan on idx_cached_rates_property_id (cost=0.00..172.65 rows=10,333 width=0) (actual time=0.534..0.534 rows=10,665 loops=4)

  • Index Cond: (property_id = lsw.property_id)
14. 394.908 394.908 ↓ 79.0 1,743,862 4

Bitmap Index Scan on idx_cached_rates_lease_term_id (cost=0.00..287.80 rows=22,084 width=0) (actual time=98.727..98.727 rows=1,743,862 loops=4)

  • Index Cond: (lease_term_id = lsw.lease_term_id)
15. 2.133 2.133 ↑ 1.0 1 2,133

Index Scan using pk_lease_terms on lease_terms lt (cost=0.28..0.31 rows=1 width=229) (actual time=0.001..0.001 rows=1 loops=2,133)

  • Index Cond: ((cid = 3,395) AND (id = cr.lease_term_id))
  • Filter: ((NOT is_disabled) AND (deleted_on IS NULL) AND is_prospect AND (occupancy_type_id <> 2) AND ((default_lease_term_id <> 201) OR (default_lease_term_id IS NULL)) AND (lease_term_type_id = 2))
16. 2.133 2.133 ↑ 1.0 1 2,133

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

  • Index Cond: (property_id = cr.property_id)
  • Filter: (cid = 3,395)
Planning time : 14.442 ms
Execution time : 421.476 ms