explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p0hh

Settings
# exclusive inclusive rows x rows loops node
1. 3.098 937.972 ↑ 16.7 12 1

GroupAggregate (cost=9,369,574.81..9,372,125.75 rows=200 width=104) (actual time=933.414..937.972 rows=12 loops=1)

  • Group Key: m.start_date
2.          

CTE month

3. 0.068 0.068 ↑ 76.9 13 1

Function Scan on generate_series start_date (cost=0.01..20.01 rows=1,000 width=16) (actual time=0.018..0.068 rows=13 loops=1)

4. 5.413 934.874 ↑ 28.5 4,466 1

Sort (cost=9,369,554.79..9,369,872.85 rows=127,222 width=108) (actual time=933.093..934.874 rows=4,466 loops=1)

  • Sort Key: m.start_date
  • Sort Method: quicksort Memory: 512kB
5. 8.802 929.461 ↑ 28.5 4,466 1

Nested Loop Left Join (cost=2,229.20..9,358,768.28 rows=127,222 width=108) (actual time=43.628..929.461 rows=4,466 loops=1)

6. 7.607 907.261 ↑ 28.5 4,466 1

Nested Loop Left Join (cost=2,228.91..9,315,732.50 rows=127,222 width=112) (actual time=43.589..907.261 rows=4,466 loops=1)

7. 11.010 408.394 ↑ 28.5 4,466 1

Nested Loop Left Join (cost=2,228.49..1,025,742.67 rows=127,222 width=68) (actual time=42.380..408.394 rows=4,466 loops=1)

8. 11.669 57.968 ↑ 28.5 4,466 1

Nested Loop (cost=2,221.78..23,455.95 rows=127,222 width=44) (actual time=40.344..57.968 rows=4,466 loops=1)

  • Join Filter: ((m.end_date >= cll.reporting_post_date) AND (m.end_date <= cll.apply_through_post_date))
  • Rows Removed by Join Filter: 11589
9. 0.097 0.097 ↑ 76.9 13 1

CTE Scan on month m (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.020..0.097 rows=13 loops=1)

10. 7.716 46.202 ↓ 1.1 1,235 13

Materialize (cost=2,221.78..3,401.31 rows=1,145 width=36) (actual time=2.420..3.554 rows=1,235 loops=13)

11. 1.663 38.486 ↓ 1.1 1,235 1

Nested Loop Left Join (cost=2,221.78..3,395.59 rows=1,145 width=36) (actual time=31.448..38.486 rows=1,235 loops=1)

12. 1.158 33.118 ↓ 1.1 1,235 1

Merge Left Join (cost=2,221.49..2,227.29 rows=1,145 width=32) (actual time=31.419..33.118 rows=1,235 loops=1)

  • Merge Cond: (cll.lease_id = cl_prior.transfer_lease_id)
  • Join Filter: ((cl_prior.cid = cll.cid) AND (cl_prior.property_id = cll.property_id))
13. 1.404 23.637 ↓ 1.1 1,235 1

Sort (cost=1,706.83..1,709.69 rows=1,145 width=32) (actual time=23.102..23.637 rows=1,235 loops=1)

  • Sort Key: cll.lease_id
  • Sort Method: quicksort Memory: 145kB
14. 22.233 22.233 ↓ 1.1 1,235 1

Index Scan using idx_cached_lease_logs_cid_property_id_lease_id_unit_space_id_le on cached_lease_logs cll (cost=0.42..1,648.65 rows=1,145 width=32) (actual time=1.356..22.233 rows=1,235 loops=1)

  • Index Cond: ((cid = 15077) AND (property_id = 598805) AND (lease_status_type_id = ANY ('{4,5,6}'::integer[])))
  • Filter: (is_post_date_ignored = 0)
  • Rows Removed by Filter: 985
15. 0.684 8.323 ↑ 46.9 26 1

Sort (cost=514.67..517.71 rows=1,219 width=16) (actual time=8.312..8.323 rows=26 loops=1)

  • Sort Key: cl_prior.transfer_lease_id
  • Sort Method: quicksort Memory: 105kB
16. 7.639 7.639 ↑ 1.0 1,195 1

Index Scan using idx_cached_leases_cid_property_id on cached_leases cl_prior (cost=0.29..452.18 rows=1,219 width=16) (actual time=0.572..7.639 rows=1,195 loops=1)

  • Index Cond: ((cid = 15077) AND (property_id = 598805))
17. 3.705 3.705 ↑ 1.0 1 1,235

Index Scan using idx_unit_spaces on unit_spaces us (cost=0.29..1.01 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,235)

  • Index Cond: (id = cll.unit_space_id)
  • Filter: ((cid = 15077) AND (cid = cll.cid))
18. 8.932 339.416 ↑ 1.0 1 4,466

GroupAggregate (cost=6.71..7.86 rows=1 width=40) (actual time=0.076..0.076 rows=1 loops=4,466)

  • Group Key: sc.cid, sc.lease_interval_id
19. 15.121 330.484 ↑ 1.0 1 4,466

Result (cost=6.71..7.84 rows=1 width=13) (actual time=0.058..0.074 rows=1 loops=4,466)

  • One-Time Filter: ((cll.lease_status_type_id = ANY ('{4,5}'::integer[])) AND (cll.lease_status_type_id <> ALL ('{1,2}'::integer[])))
20. 94.948 315.363 ↓ 2.0 2 3,391

Bitmap Heap Scan on scheduled_charges sc (cost=6.71..7.84 rows=1 width=13) (actual time=0.074..0.093 rows=2 loops=3,391)

  • Recheck Cond: ((lease_interval_id = cll.active_lease_interval_id) AND ((lease_id = cll.lease_id) OR (lease_id = cl_prior.id)))
  • Filter: ((NOT is_unselected_quote) AND (deleted_on IS NULL) AND (ar_trigger_id >= 300) AND (ar_trigger_id <= 399) AND (m.end_date >= charge_start_date) AND (m.end_date <= COALESCE(charge_end_date, '2099-12-31'::date)) AND (cid = cll.cid) AND (ar_code_type_id = 2))
  • Rows Removed by Filter: 23
  • Heap Blocks: exact=22623
21. 8.154 220.415 ↓ 0.0 0 3,391

BitmapAnd (cost=6.70..6.70 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=3,391)

22. 118.685 118.685 ↑ 3.1 25 3,391

Bitmap Index Scan on idx_scheduled_charges_lease_interval_id (cost=0.00..2.11 rows=77 width=0) (actual time=0.035..0.035 rows=25 loops=3,391)

  • Index Cond: (lease_interval_id = cll.active_lease_interval_id)
23. 3.342 93.576 ↓ 0.0 0 3,342

BitmapOr (cost=4.35..4.35 rows=173 width=0) (actual time=0.028..0.028 rows=0 loops=3,342)

24. 86.892 86.892 ↓ 1.0 87 3,342

Bitmap Index Scan on idx_scheduled_charges_lease_id (cost=0.00..2.17 rows=86 width=0) (actual time=0.026..0.026 rows=87 loops=3,342)

  • Index Cond: (lease_id = cll.lease_id)
25. 3.342 3.342 ↑ 43.0 2 3,342

Bitmap Index Scan on idx_scheduled_charges_lease_id (cost=0.00..2.17 rows=86 width=0) (actual time=0.001..0.001 rows=2 loops=3,342)

  • Index Cond: (lease_id = cl_prior.id)
26. 31.262 491.260 ↑ 1.0 1 4,466

GroupAggregate (cost=0.42..65.14 rows=1 width=72) (actual time=0.109..0.110 rows=1 loops=4,466)

  • Group Key: at.cid, at.lease_interval_id
27. 459.998 459.998 ↓ 5.0 5 4,466

Index Scan using idx_ar_transactions_lease_id on ar_transactions at (cost=0.42..65.08 rows=1 width=21) (actual time=0.061..0.103 rows=5 loops=4,466)

  • Index Cond: (lease_id = cll.lease_id)
  • Filter: ((NOT is_temporary) AND (NOT is_reversal) AND (NOT is_deleted) AND (ar_code_type_id <> 1) AND (post_date >= m.start_date) AND (post_date <= m.end_date) AND (cid = cll.cid) AND (lease_interval_id = cll.active_lease_interval_id))
  • Rows Removed by Filter: 78
28. 13.398 13.398 ↑ 1.0 1 4,466

Index Scan using pk_property_units on property_units pu (cost=0.29..0.34 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4,466)

  • Index Cond: ((cid = us.cid) AND (cid = 15077) AND (id = us.property_unit_id))