explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QTJR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 40,501.705 875,703.900 ↑ 69.7 5,539 1

HashAggregate (cost=7,694,024.24..7,698,849.70 rows=386,037 width=44) (actual time=875,698.531..875,703.900 rows=5,539 loops=1)

  • Group Key: at.cid, at.property_id, at.ar_code_id
2. 280,886.322 835,202.195 ↓ 17.0 65,529,685 1

HashAggregate (cost=7,529,958.64..7,616,816.90 rows=3,860,367 width=56) (actual time=785,822.010..835,202.195 rows=65,529,685 loops=1)

  • Group Key: at.cid, at.id, gs.start_date
3. 245,956.711 554,315.873 ↓ 21.5 83,041,095 1

Merge Semi Join (cost=1,001.72..7,501,005.89 rows=3,860,367 width=38) (actual time=6.421..554,315.873 rows=83,041,095 loops=1)

  • Merge Cond: ((load_prop.property_id = cll.property_id) AND (at.lease_id = cll.lease_id))
  • Join Filter: ((cll.reporting_post_month <= gs.start_date) AND (cll.apply_through_post_month >= gs.start_date))
  • Rows Removed by Join Filter: 342,891,855
4. 83,080.118 174,934.429 ↓ 1.3 83,041,095 1

Nested Loop (cost=1,001.16..7,242,360.73 rows=65,626,240 width=46) (actual time=6.369..174,934.429 rows=83,041,095 loops=1)

5. 20,965.514 58,637.873 ↓ 1.3 16,608,219 1

Merge Join (cost=1,001.16..6,422,031.59 rows=13,125,248 width=38) (actual time=6.359..58,637.873 rows=16,608,219 loops=1)

  • Merge Cond: (at.property_id = load_prop.property_id)
6. 0.000 31,310.034 ↓ 1.3 16,609,007 1

Gather Merge (cost=1,001.02..6,192,312.48 rows=13,125,248 width=34) (actual time=6.329..31,310.034 rows=16,609,007 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 7,282.432 31,771.230 ↓ 1.0 5,536,336 3 / 3

Nested Loop Left Join (cost=1.00..4,676,333.16 rows=5,468,853 width=34) (actual time=0.110..31,771.230 rows=5,536,336 loops=3)

8. 7,013.197 7,013.197 ↑ 1.3 4,368,900 3 / 3

Parallel Index Scan Backward using idx_ar_transactions_allocations_join on ar_transactions at (cost=0.56..1,284,528.30 rows=5,468,853 width=34) (actual time=0.052..7,013.197 rows=4,368,900 loops=3)

  • Index Cond: (cid = 13,531)
  • Filter: ((NOT is_temporary) AND (post_month <= '2020-06-01'::date))
  • Rows Removed by Filter: 125,107
9. 17,475.601 17,475.601 ↑ 1.0 1 13,106,701 / 3

Index Scan using idx_ar_allocations_charge_ar_transaction_id on ar_allocations aa (cost=0.43..0.61 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=13,106,701)

  • Index Cond: (at.id = charge_ar_transaction_id)
  • Filter: ((cid = 13,531) AND (cid = at.cid) AND (property_id = at.property_id))
10. 6,361.988 6,362.325 ↓ 81,413.2 16,608,284 1

Materialize (cost=0.14..30.18 rows=204 width=4) (actual time=0.026..6,362.325 rows=16,608,284 loops=1)

11. 0.337 0.337 ↑ 1.1 189 1

Index Only Scan using idx_load_prop_property_id on load_prop (cost=0.14..29.67 rows=204 width=4) (actual time=0.016..0.337 rows=189 loops=1)

  • Heap Fetches: 189
12. 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)

13. 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
14. 130,048.425 133,424.733 ↓ 165.3 344,121,667 1

Materialize (cost=0.56..485,641.44 rows=2,081,346 width=20) (actual time=0.037..133,424.733 rows=344,121,667 loops=1)

15. 3,376.308 3,376.308 ↑ 1.0 2,063,777 1

Index Only Scan using idx_cached_lease_logs_cid_is_post_month_ignored_property_id_lea on cached_lease_logs cll (cost=0.56..480,438.07 rows=2,081,346 width=20) (actual time=0.035..3,376.308 rows=2,063,777 loops=1)

  • Index Cond: ((cid = 13,531) AND (is_post_month_ignored = 0))
  • Filter: (lease_status_type_id = ANY ('{1,2,3,4,5,6}'::integer[]))
  • Heap Fetches: 2,063,777