explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1wi

Settings
# exclusive inclusive rows x rows loops node
1. 79.573 399,145.642 ↑ 184.4 258 1

GroupAggregate (cost=16,434,018.03..18,468,535.92 rows=47,570 width=56) (actual time=399,036.185..399,145.642 rows=258 loops=1)

  • Group Key: cal.cid, cal.property_id, cpw.id
2. 87.400 399,066.069 ↑ 142.0 76,384 1

Sort (cost=16,434,018.03..16,461,138.60 rows=10,848,225 width=48) (actual time=399,035.811..399,066.069 rows=76,384 loops=1)

  • Sort Key: cal.property_id, cpw.id
  • Sort Method: quicksort Memory: 12,935kB
3. 8,931.249 398,978.669 ↑ 142.0 76,384 1

Merge Join (cost=12,605,953.37..14,832,640.10 rows=10,848,225 width=48) (actual time=374,664.829..398,978.669 rows=76,384 loops=1)

  • Merge Cond: (cal.application_id = cal_prior_inner.application_id)
  • Join Filter: ((cpw.prior_end_date >= cal_prior_inner.reporting_post_date) AND (cpw.prior_end_date <= cal_prior_inner.apply_through_post_date) AND (cal_prior_inner.id <> cal.id))
  • Rows Removed by Join Filter: 15,208,733
4. 2,325.231 172,087.513 ↓ 1.1 1,080,775 1

Sort (cost=5,786,550.14..5,788,905.30 rows=942,061 width=44) (actual time=171,606.532..172,087.513 rows=1,080,775 loops=1)

  • Sort Key: cal.application_id
  • Sort Method: quicksort Memory: 133,588kB
5. 6,759.133 169,762.282 ↓ 1.1 1,080,775 1

Hash Join (cost=206,112.68..5,693,071.97 rows=942,061 width=44) (actual time=31,767.209..169,762.282 rows=1,080,775 loops=1)

  • Hash Cond: (cal.property_id = cpw.property_id)
  • Join Filter: ((cpw.end_date >= cal.reporting_post_date) AND (cpw.end_date <= cal.apply_through_post_date))
  • Rows Removed by Join Filter: 14,268,624
6. 132,982.911 163,002.271 ↓ 1.9 8,017,286 1

Bitmap Heap Scan on cached_application_logs cal (cost=206,070.92..5,544,595.93 rows=4,251,215 width=40) (actual time=31,766.228..163,002.271 rows=8,017,286 loops=1)

  • Recheck Cond: (NOT is_post_date_ignored)
  • Filter: (CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 1) THEN false ELSE true END AND (cid = 15,077) AND (lease_interval_type_id = 1) AND (occupancy_type_id = ANY ('{1,2,6,9,10}'::integer[])))
  • Rows Removed by Filter: 2,018,275
  • Heap Blocks: exact=4,565,709
7. 30,019.295 30,019.295 ↑ 1.0 10,035,563 1

Bitmap Index Scan on idx_cached_application_logs_property_id_reporting_daterange (cost=0.00..204,994.16 rows=10,084,346 width=0) (actual time=30,019.295..30,019.295 rows=10,035,563 loops=1)

8.          

SubPlan (for Bitmap Heap Scan)

9. 0.065 0.065 ↑ 1.2 6 1

Seq Scan on list_items li (cost=0.00..13.94 rows=7 width=4) (actual time=0.033..0.065 rows=6 loops=1)

  • Filter: ((cid = 15,077) AND (default_list_item_id = ANY ('{66,65,90}'::integer[])))
  • Rows Removed by Filter: 298
10. 0.177 0.878 ↑ 1.0 355 1

Hash (cost=37.32..37.32 rows=355 width=28) (actual time=0.877..0.878 rows=355 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
11. 0.337 0.701 ↑ 1.0 355 1

Hash Join (cost=21.01..37.32 rows=355 width=28) (actual time=0.205..0.701 rows=355 loops=1)

  • Hash Cond: (cpw.property_id = load_prop.property_id)
12. 0.173 0.173 ↑ 1.0 355 1

Seq Scan on property_weeks cpw (cost=0.00..11.44 rows=355 width=24) (actual time=0.007..0.173 rows=355 loops=1)

  • Filter: (cid = 15,077)
13. 0.083 0.191 ↑ 1.0 178 1

Hash (cost=18.78..18.78 rows=178 width=4) (actual time=0.191..0.191 rows=178 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
14. 0.108 0.108 ↑ 1.0 178 1

Seq Scan on load_prop (cost=0.00..18.78 rows=178 width=4) (actual time=0.004..0.108 rows=178 loops=1)

15. 10,396.809 217,959.907 ↓ 1.8 17,677,344 1

Materialize (cost=6,819,403.23..6,869,824.96 rows=10,084,346 width=32) (actual time=203,032.610..217,959.907 rows=17,677,344 loops=1)

16. 40,433.974 207,563.098 ↑ 1.0 10,035,561 1

Sort (cost=6,819,403.23..6,844,614.09 rows=10,084,346 width=32) (actual time=203,032.601..207,563.098 rows=10,035,561 loops=1)

  • Sort Key: cal_prior_inner.application_id
  • Sort Method: external merge Disk: 412,432kB
17. 167,129.124 167,129.124 ↑ 1.0 10,035,561 1

Seq Scan on cached_application_logs cal_prior_inner (cost=0.00..5,405,034.71 rows=10,084,346 width=32) (actual time=0.028..167,129.124 rows=10,035,561 loops=1)

  • Filter: ((NOT is_post_date_ignored) AND (cid = 15,077))
  • Rows Removed by Filter: 15,351,991
Planning time : 5.455 ms