explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HbAO

Settings
# exclusive inclusive rows x rows loops node
1. 75.476 428,913.063 ↑ 184.4 258 1

GroupAggregate (cost=16,433,992.15..18,468,510.03 rows=47,570 width=56) (actual time=428,803.433..428,913.063 rows=258 loops=1)

  • Group Key: cal.cid, cal.property_id, cpw.id
2. 94.172 428,837.587 ↑ 142.0 76,384 1

Sort (cost=16,433,992.15..16,461,112.71 rows=10,848,225 width=48) (actual time=428,803.038..428,837.587 rows=76,384 loops=1)

  • Sort Key: cal.property_id, cpw.id
  • Sort Method: quicksort Memory: 12,935kB
3. 9,068.358 428,743.415 ↑ 142.0 76,384 1

Merge Join (cost=12,605,927.49..14,832,614.22 rows=10,848,225 width=48) (actual time=404,250.426..428,743.415 rows=76,384 loops=1)

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

Sort (cost=5,786,524.26..5,788,879.41 rows=942,061 width=44) (actual time=241,107.353..241,605.432 rows=1,080,775 loops=1)

  • Sort Key: cal.application_id
  • Sort Method: quicksort Memory: 144,086kB
5. 7,065.273 239,290.434 ↓ 1.1 1,080,775 1

Hash Join (cost=206,086.80..5,693,046.08 rows=942,061 width=44) (actual time=19,392.570..239,290.434 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. 214,583.744 232,224.785 ↓ 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=19,392.098..232,224.785 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. 17,640.982 17,640.982 ↑ 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=17,640.982..17,640.982 rows=10,035,563 loops=1)

8.          

SubPlan (for Bitmap Heap Scan)

9. 0.059 0.059 ↑ 1.2 6 1

Seq Scan on list_items li (cost=0.00..13.94 rows=7 width=4) (actual time=0.030..0.059 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.174 0.376 ↑ 1.0 355 1

Hash (cost=11.44..11.44 rows=355 width=24) (actual time=0.375..0.376 rows=355 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
11. 0.202 0.202 ↑ 1.0 355 1

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

  • Filter: (cid = 15,077)
12. 10,442.837 178,069.625 ↓ 1.8 17,677,344 1

Materialize (cost=6,819,403.23..6,869,824.96 rows=10,084,346 width=32) (actual time=163,117.251..178,069.625 rows=17,677,344 loops=1)

13. 29,876.215 167,626.788 ↑ 1.0 10,035,561 1

Sort (cost=6,819,403.23..6,844,614.09 rows=10,084,346 width=32) (actual time=163,117.244..167,626.788 rows=10,035,561 loops=1)

  • Sort Key: cal_prior.application_id
  • Sort Method: external merge Disk: 412,432kB
14. 137,750.573 137,750.573 ↑ 1.0 10,035,561 1

Seq Scan on cached_application_logs cal_prior (cost=0.00..5,405,034.71 rows=10,084,346 width=32) (actual time=0.031..137,750.573 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.074 ms