explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ruTu

Settings
# exclusive inclusive rows x rows loops node
1. 342.913 20,968.660 ↓ 192,660.0 192,660 1

GroupAggregate (cost=87,274.82..105,908.01 rows=1 width=40) (actual time=992.010..20,968.660 rows=192,660 loops=1)

  • Group Key: sc.cid, sc.lease_id
2. 322.487 20,625.747 ↓ 196,877.0 196,877 1

Nested Loop (cost=87,274.82..105,907.99 rows=1 width=12) (actual time=991.973..20,625.747 rows=196,877 loops=1)

3. 406.036 19,909.506 ↓ 196,877.0 196,877 1

Nested Loop (cost=87,274.67..105,907.82 rows=1 width=16) (actual time=991.963..19,909.506 rows=196,877 loops=1)

4. 230.318 1,852.529 ↓ 452.1 229,233 1

Subquery Scan on lease_interval (cost=87,265.46..99,184.42 rows=507 width=12) (actual time=991.908..1,852.529 rows=229,233 loops=1)

  • Filter: ((lease_interval.lease_status_type_id <> 2) AND (lease_interval.row_number = 1) AND (lease_interval.cid = 13,531))
  • Rows Removed by Filter: 108,184
5. 462.099 1,622.211 ↓ 1.1 337,417 1

WindowAgg (cost=87,265.46..93,969.87 rows=297,974 width=28) (actual time=991.906..1,622.211 rows=337,417 loops=1)

6. 463.062 1,160.112 ↓ 1.1 337,417 1

Sort (cost=87,265.46..88,010.39 rows=297,974 width=20) (actual time=991.898..1,160.112 rows=337,417 loops=1)

  • Sort Key: li.lease_id, li.lease_start_date DESC, li.id DESC
  • Sort Method: quicksort Memory: 37,113kB
7. 293.334 697.050 ↓ 1.1 337,417 1

Hash Join (cost=11.95..60,172.43 rows=297,974 width=20) (actual time=0.241..697.050 rows=337,417 loops=1)

  • Hash Cond: (li.property_id = load_prop_1.property_id)
8. 403.490 403.490 ↓ 1.1 338,827 1

Seq Scan on lease_intervals li (cost=0.00..56,063.34 rows=297,974 width=24) (actual time=0.007..403.490 rows=338,827 loops=1)

  • Filter: ((lease_status_type_id <> 2) AND (lease_interval_type_id <> 4) AND (lease_start_date < '2020-09-30'::date) AND (lease_end_date < '2020-09-30'::date) AND (cid = 13,531))
  • Rows Removed by Filter: 1,109,588
9. 0.103 0.226 ↑ 1.0 220 1

Hash (cost=9.20..9.20 rows=220 width=4) (actual time=0.226..0.226 rows=220 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
10. 0.123 0.123 ↑ 1.0 220 1

Seq Scan on load_prop load_prop_1 (cost=0.00..9.20 rows=220 width=4) (actual time=0.003..0.123 rows=220 loops=1)

11. 14,900.145 17,650.941 ↑ 1.0 1 229,233

Bitmap Heap Scan on scheduled_charges sc (cost=9.21..13.25 rows=1 width=20) (actual time=0.068..0.077 rows=1 loops=229,233)

  • Recheck Cond: ((lease_interval_id = lease_interval.prior_lease_interval_id) AND (lease_id = lease_interval.lease_id))
  • Filter: ((deleted_by IS NULL) AND (NOT is_unselected_quote) AND (charge_start_date < '2020-10-15'::date) AND ((deleted_on IS NULL) OR (deleted_on > '2020-09-30 00:00:00-06'::timestamp with time zone)) AND (cid = 13,531) AND (ar_code_type_id = 2) AND (ar_trigger_id = ANY ('{301,302,303,304,305,306,307,308,309,310}'::integer[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=597,158
12. 541.350 2,750.796 ↓ 0.0 0 229,233

BitmapAnd (cost=9.21..9.21 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=229,233)

13. 1,604.631 1,604.631 ↑ 4.0 3 229,233

Bitmap Index Scan on idx_scheduled_charges_lease_interval_id (cost=0.00..4.43 rows=12 width=0) (actual time=0.007..0.007 rows=3 loops=229,233)

  • Index Cond: (lease_interval_id = lease_interval.prior_lease_interval_id)
14. 604.815 604.815 ↑ 2.1 13 201,605

Bitmap Index Scan on idx_scheduled_charges_lease_id (cost=0.00..4.54 rows=27 width=0) (actual time=0.003..0.003 rows=13 loops=201,605)

  • Index Cond: (lease_id = lease_interval.lease_id)
15. 393.754 393.754 ↑ 1.0 1 196,877

Index Only Scan using idx_load_prop_property_id on load_prop (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=196,877)

  • Index Cond: (property_id = sc.property_id)
  • Heap Fetches: 196,877
Planning time : 10.709 ms