explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GAu9

Settings
# exclusive inclusive rows x rows loops node
1. 340.920 16,000.672 ↓ 2.6 292,549 1

Hash Join (cost=580,238.02..1,500,662.53 rows=110,968 width=128) (actual time=8,271.331..16,000.672 rows=292,549 loops=1)

  • Hash Cond: (cal.property_id = load_prop.property_id)
2. 7,570.510 15,659.191 ↓ 2.3 293,753 1

Bitmap Heap Scan on cached_application_logs cal (cost=580,224.13..1,499,064.89 rows=126,419 width=105) (actual time=8,270.760..15,659.191 rows=293,753 loops=1)

  • Recheck Cond: ((apply_through_post_date >= '2019-12-31'::date) AND (post_date >= '2019-01-01'::date))
  • Filter: ((NOT is_post_date_ignored) AND (application_status_id <> ALL ('{5,6}'::integer[])) AND (lease_interval_type_id <> 4) AND CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 1) THEN false ELSE true END AND (reporting_post_date < '2020-01-01 00:00:00'::timestamp without time zone) AND (cid = 3395))
  • Rows Removed by Filter: 331881
  • Heap Blocks: exact=545690
3. 221.279 8,087.544 ↓ 0.0 0 1

BitmapAnd (cost=580,214.48..580,214.48 rows=273,775 width=0) (actual time=8,087.544..8,087.544 rows=0 loops=1)

4. 1,146.010 1,146.010 ↑ 1.1 2,384,855 1

Bitmap Index Scan on idx_cached_application_logs_apply_through_post_date (cost=0.00..50,322.19 rows=2,724,483 width=0) (actual time=1,146.009..1,146.010 rows=2,384,855 loops=1)

  • Index Cond: (apply_through_post_date >= '2019-12-31'::date)
5. 2,844.044 2,844.044 ↓ 1.0 11,259,249 1

Bitmap Index Scan on idx_cached_application_logs_post_date (cost=0.00..207,034.51 rows=11,209,859 width=0) (actual time=2,844.044..2,844.044 rows=11,259,249 loops=1)

  • Index Cond: (post_date >= '2019-01-01'::date)
6. 3,876.211 3,876.211 ↑ 1.0 17,424,558 1

Bitmap Index Scan on idx_cached_application_logs_is_post_date_ignored (cost=0.00..322,762.47 rows=17,475,988 width=0) (actual time=3,876.210..3,876.211 rows=17,424,558 loops=1)

  • Index Cond: (is_post_date_ignored = false)
7.          

SubPlan (forBitmap Heap Scan)

8. 1.137 1.137 ↑ 1.3 3 1

Seq Scan on list_items li (cost=0.00..9.63 rows=4 width=4) (actual time=0.537..1.137 rows=3 loops=1)

  • Filter: ((default_list_item_id = ANY ('{66,65}'::integer[])) AND (cid = 3395))
  • Rows Removed by Filter: 306
9. 0.285 0.561 ↑ 1.0 395 1

Hash (cost=8.95..8.95 rows=395 width=27) (actual time=0.561..0.561 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
10. 0.276 0.276 ↑ 1.0 395 1

Seq Scan on load_prop (cost=0.00..8.95 rows=395 width=27) (actual time=0.009..0.276 rows=395 loops=1)

Planning time : 3.039 ms