explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4966

Settings
# exclusive inclusive rows x rows loops node
1. 90.459 430,015.001 ↑ 184.4 258 1

GroupAggregate (cost=16,433,992.15..18,468,510.03 rows=47,570 width=56) (actual time=429,886.715..430,015.001 rows=258 loops=1)

  • Group Key: cal.cid, cal.property_id, cpw.id
2. 95.104 429,924.542 ↑ 142.0 76,384 1

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

  • Sort Key: cal.property_id, cpw.id
  • Sort Method: quicksort Memory: 12,935kB
3. 9,051.819 429,829.438 ↑ 142.0 76,384 1

Merge Join (cost=12,605,927.49..14,832,614.22 rows=10,848,225 width=48) (actual time=405,434.182..429,829.438 rows=76,384 loops=1)

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

Sort (cost=5,786,524.26..5,788,879.41 rows=942,061 width=44) (actual time=248,132.205..248,616.180 rows=1,080,775 loops=1)

  • Sort Key: cal.application_id
  • Sort Method: quicksort Memory: 144,086kB
5. 7,086.140 245,513.986 ↓ 1.1 1,080,775 1

Hash Join (cost=206,086.80..5,693,046.08 rows=942,061 width=44) (actual time=37,162.258..245,513.986 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. 204,597.529 238,427.447 ↓ 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=37,161.757..238,427.447 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. 33,829.858 33,829.858 ↑ 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=33,829.857..33,829.858 rows=10,035,563 loops=1)

8.          

SubPlan (for Bitmap Heap Scan)

9. 0.060 0.060 ↑ 1.2 6 1

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

Hash (cost=11.44..11.44 rows=355 width=24) (actual time=0.398..0.399 rows=355 loops=1)

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

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

  • Filter: (cid = 15,077)
12. 10,389.623 172,161.439 ↓ 1.8 17,677,344 1

Materialize (cost=6,819,403.23..6,869,824.96 rows=10,084,346 width=32) (actual time=157,276.297..172,161.439 rows=17,677,344 loops=1)

13. 34,002.836 161,771.816 ↑ 1.0 10,035,561 1

Sort (cost=6,819,403.23..6,844,614.09 rows=10,084,346 width=32) (actual time=157,276.291..161,771.816 rows=10,035,561 loops=1)

  • Sort Key: cal_prior_inner.application_id
  • Sort Method: external merge Disk: 412,432kB
14. 127,768.980 127,768.980 ↑ 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.030..127,768.980 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 : 20.844 ms