explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aa8L

Settings
# exclusive inclusive rows x rows loops node
1. 5,825.482 12,187.281 ↓ 23,168.2 278,019 1

Nested Loop Left Join (cost=2,246.47..164,217.87 rows=12 width=637) (actual time=28.101..12,187.281 rows=278,019 loops=1)

2. 199.891 6,083.780 ↓ 23,168.2 278,019 1

Nested Loop (cost=2,246.18..164,198.74 rows=12 width=809) (actual time=28.007..6,083.780 rows=278,019 loops=1)

3. 42.251 894.253 ↓ 32.3 97,836 1

Nested Loop (cost=2,245.62..77,593.03 rows=3,033 width=537) (actual time=27.974..894.253 rows=97,836 loops=1)

4. 0.128 0.128 ↑ 1.0 23 1

Seq Scan on load_prop (cost=0.00..1.23 rows=23 width=517) (actual time=0.011..0.128 rows=23 loops=1)

5. 334.435 851.874 ↓ 32.2 4,254 23

Bitmap Heap Scan on cached_applications ca_updated (cost=2,245.62..3,372.24 rows=132 width=20) (actual time=23.103..37.038 rows=4,254 loops=23)

  • Recheck Cond: ((property_id = load_prop.property_id) AND (cid = 8132))
  • Filter: (CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 1) THEN false ELSE true END AND (occupancy_type_id <> 4) AND (lease_interval_type_id = 1))
  • Rows Removed by Filter: 573
  • Heap Blocks: exact=86057
6. 8.878 517.339 ↓ 0.0 0 23

BitmapAnd (cost=2,095.33..2,095.33 rows=297 width=0) (actual time=22.493..22.493 rows=0 loops=23)

7. 20.378 20.378 ↓ 1.8 4,832 23

Bitmap Index Scan on idx_cached_applications_property_id (cost=0.00..51.57 rows=2,692 width=0) (actual time=0.886..0.886 rows=4,832 loops=23)

  • Index Cond: (property_id = load_prop.property_id)
8. 488.083 488.083 ↓ 1.0 111,075 23

Bitmap Index Scan on pk_cached_applications (cost=0.00..2,031.31 rows=109,718 width=0) (actual time=21.221..21.221 rows=111,075 loops=23)

  • Index Cond: (cid = 8132)
9.          

SubPlan (forBitmap Heap Scan)

10. 0.074 0.100 ↑ 1.5 2 1

Bitmap Heap Scan on list_items li (cost=5.10..150.28 rows=3 width=4) (actual time=0.099..0.100 rows=2 loops=1)

  • Recheck Cond: (cid = 8132)
  • Filter: (default_list_item_id = ANY ('{66,65}'::integer[]))
  • Rows Removed by Filter: 101
  • Heap Blocks: exact=22
11. 0.026 0.026 ↑ 1.1 103 1

Bitmap Index Scan on idx_list_items_cid_list_type_id (cost=0.00..5.10 rows=109 width=0) (actual time=0.026..0.026 rows=103 loops=1)

  • Index Cond: (cid = 8132)
12. 4,989.636 4,989.636 ↓ 3.0 3 97,836

Index Scan using idx_events_cid_lease_interval_id_event_datetime on events e (cost=0.56..28.54 rows=1 width=288) (actual time=0.017..0.051 rows=3 loops=97,836)

  • Index Cond: ((cid = 8132) AND (lease_interval_id = ca_updated.lease_interval_id))
  • Filter: ((NOT is_deleted) AND (ca_updated.property_id = property_id) AND CASE WHEN (event_type_id = ANY ('{3,7,10,395}'::integer[])) THEN ((event_datetime >= '2018-01-01 00:00:00-07'::timestamp with time zone) AND (event_datetime <= '2018-12-31 23:59:59-07'::timestamp with time zone)) WHEN (event_type_id = ANY ('{2,4}'::integer[])) THEN ((event_datetime >= '2018-01-01 00:00:00-07'::timestamp with time zone) AND (event_datetime <= '2019-01-01 23:59:59'::timestamp without time zone)) WHEN (event_type_id = ANY ('{9,17,78,13,131,12}'::integer[])) THEN true ELSE false END AND CASE WHEN (event_type_id = 2) THEN (util_to_bool((details ->> 'is_system_generated'::text)) IS DISTINCT FROM true) ELSE true END)
  • Rows Removed by Filter: 14
13. 278.019 278.019 ↓ 0.0 0 278,019

Index Scan using idx_event_results on event_results er (cost=0.28..0.31 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=278,019)

  • Index Cond: (id = e.event_result_id)
  • Filter: ((cid = 8132) AND (cid = e.cid))
  • Rows Removed by Filter: 0
Planning time : 3.217 ms