explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xD9r : Optimization for: plan #QTJR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 43,484.472 445,802.057 ↑ 296.2 5,539 1

HashAggregate (cost=4,000,251.55..4,020,759.75 rows=1,640,656 width=44) (actual time=445,790.003..445,802.057 rows=5,539 loops=1)

  • Group Key: at.cid, at.property_id, at.ar_code_id
2. 76,859.072 402,317.585 ↓ 4.0 65,529,685 1

Group (cost=3,302,972.75..3,672,120.35 rows=16,406,560 width=56) (actual time=280,967.926..402,317.585 rows=65,529,685 loops=1)

  • Group Key: at.cid, at.id, gs.start_date
3. 127,140.025 325,458.513 ↓ 5.1 83,041,095 1

Sort (cost=3,302,972.75..3,343,989.15 rows=16,406,560 width=38) (actual time=280,967.921..325,458.513 rows=83,041,095 loops=1)

  • Sort Key: at.id, gs.start_date
  • Sort Method: external merge Disk: 3,942,808kB
4. 86,991.119 198,318.488 ↓ 5.1 83,041,095 1

Nested Loop (cost=104,264.09..888,210.01 rows=16,406,560 width=38) (actual time=48,178.920..198,318.488 rows=83,041,095 loops=1)

5. 23,072.764 78,110.931 ↓ 5.1 16,608,219 1

Hash Right Join (cost=104,264.09..683,126.87 rows=3,281,312 width=30) (actual time=48,178.907..78,110.931 rows=16,608,219 loops=1)

  • Hash Cond: ((aa.cid = at.cid) AND (aa.charge_ar_transaction_id = at.id) AND (aa.property_id = at.property_id))
6. 6,868.020 6,868.020 ↑ 1.0 11,634,353 1

Seq Scan on ar_allocations aa (cost=0.00..360,308.16 rows=11,635,373 width=16) (actual time=0.026..6,868.020 rows=11,634,353 loops=1)

  • Filter: (cid = 13,531)
7. 9,101.893 48,170.147 ↓ 4.0 13,105,937 1

Hash (cost=46,841.13..46,841.13 rows=3,281,312 width=30) (actual time=48,170.146..48,170.147 rows=13,105,937 loops=1)

  • Buckets: 8,388,608 (originally 4194304) Batches: 2 (originally 1) Memory Usage: 475,351kB
8. 10,998.278 39,068.254 ↓ 4.0 13,105,937 1

Nested Loop (cost=45,148.03..46,841.13 rows=3,281,312 width=30) (actual time=2,185.393..39,068.254 rows=13,105,937 loops=1)

  • Join Filter: (load_prop.property_id = at.property_id)
9. 423.312 2,916.104 ↓ 4,326.4 441,296 1

Hash Join (cost=45,147.47..46,097.47 rows=102 width=12) (actual time=2,185.312..2,916.104 rows=441,296 loops=1)

  • Hash Cond: (cll.property_id = load_prop.property_id)
10. 1,611.720 2,492.588 ↓ 11.1 442,385 1

HashAggregate (cost=45,135.88..45,535.88 rows=40,000 width=8) (actual time=2,185.098..2,492.588 rows=442,385 loops=1)

  • Group Key: cll.property_id, cll.lease_id
11. 880.868 880.868 ↑ 1.0 2,211,925 1

Seq Scan on test_cll cll (cost=0.00..34,076.25 rows=2,211,925 width=8) (actual time=0.008..880.868 rows=2,211,925 loops=1)

12. 0.096 0.204 ↑ 1.0 204 1

Hash (cost=9.04..9.04 rows=204 width=4) (actual time=0.203..0.204 rows=204 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
13. 0.108 0.108 ↑ 1.0 204 1

Seq Scan on load_prop (cost=0.00..9.04 rows=204 width=4) (actual time=0.010..0.108 rows=204 loops=1)

14. 25,153.872 25,153.872 ↓ 30.0 30 441,296

Index Scan using idx_ar_transactions_allocations_join on ar_transactions at (cost=0.56..7.28 rows=1 width=34) (actual time=0.015..0.057 rows=30 loops=441,296)

  • Index Cond: ((cid = 13,531) AND (property_id = cll.property_id) AND (lease_id = cll.lease_id))
  • Filter: ((NOT is_temporary) AND (post_month <= '2020-06-01'::date))
  • Rows Removed by Filter: 1
15. 33,216.428 33,216.438 ↑ 1.0 5 16,608,219

Materialize (cost=0.00..1.15 rows=5 width=8) (actual time=0.000..0.002 rows=5 loops=16,608,219)

16. 0.010 0.010 ↑ 1.0 5 1

Seq Scan on generate_series gs (cost=0.00..1.12 rows=5 width=8) (actual time=0.006..0.010 rows=5 loops=1)

  • Filter: (is_current = 1)
  • Rows Removed by Filter: 5