explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GC7w

Settings
# exclusive inclusive rows x rows loops node
1. 5,685.372 11,559.143 ↓ 11,079.5 265,908 1

Nested Loop Left Join (cost=2,246.47..170,075.68 rows=24 width=637) (actual time=25.404..11,559.143 rows=265,908 loops=1)

2. 251.803 5,607.863 ↓ 11,079.5 265,908 1

Nested Loop (cost=2,246.19..170,037.45 rows=24 width=809) (actual time=25.171..5,607.863 rows=265,908 loops=1)

3. 41.706 855.604 ↓ 32.3 97,836 1

Nested Loop (cost=2,245.63..77,593.21 rows=3,033 width=537) (actual time=25.112..855.604 rows=97,836 loops=1)

4. 0.158 0.158 ↑ 1.0 23 1

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

5. 328.826 813.740 ↓ 32.2 4,254 23

Bitmap Heap Scan on cached_applications ca_updated (cost=2,245.63..3,372.24 rows=132 width=20) (actual time=21.670..35.380 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.763 484.794 ↓ 0.0 0 23

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

7. 19.136 19.136 ↓ 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.832..0.832 rows=4,832 loops=23)

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

Bitmap Index Scan on pk_cached_applications (cost=0.00..2,031.32 rows=109,719 width=0) (actual time=19.865..19.865 rows=111,075 loops=23)

  • Index Cond: (cid = 8132)
9.          

SubPlan (forBitmap Heap Scan)

10. 0.098 0.120 ↑ 1.5 2 1

Bitmap Heap Scan on list_items li (cost=5.10..150.28 rows=3 width=4) (actual time=0.119..0.120 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.022 0.022 ↑ 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.022..0.022 rows=103 loops=1)

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

Index Scan using idx_events_cid_lease_interval_id_event_datetime on events e (cost=0.56..30.47 rows=1 width=288) (actual time=0.018..0.046 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 = 3) THEN ((util_to_int(((details -> 'event_call'::text) ->> 'call_result_id'::text)) = 6) AND (ps_product_id = ANY ('{36,46}'::integer[])) AND ((event_sub_type_id <> 22) OR (event_sub_type_id IS NULL)) AND (data_reference_id IS NOT NULL) AND (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 = 7) THEN ((util_to_int((details ->> 'destination_ps_product_id'::text)) = ANY ('{46,55}'::bigint[])) AND ((event_sub_type_id <> 106) OR (event_sub_type_id IS NULL)) AND (data_reference_id IS NOT NULL) AND (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 ('{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)
  • Rows Removed by Filter: 14
13. 265.908 265.908 ↓ 0.0 0 265,908

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=265,908)

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