explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QcGw

Settings
# exclusive inclusive rows x rows loops node
1. 10.053 29,888.089 ↑ 1.0 1 1

GroupAggregate (cost=651.63..46,073.08 rows=1 width=595) (actual time=29,888.089..29,888.089 rows=1 loops=1)

  • Output: ((wo.extension_data ->> 'ProfitCenter'::text)), sr.description, count(*)
  • Group Key: (wo.extension_data ->> 'ProfitCenter'::text), sr.description
  • Buffers: shared hit=10,282,923 read=637,802 written=2
2. 681.862 29,878.036 ↓ 10,831.0 10,831 1

Nested Loop (cost=651.63..46,073.06 rows=1 width=595) (actual time=4,960.691..29,878.036 rows=10,831 loops=1)

  • Output: (wo.extension_data ->> 'ProfitCenter'::text), sr.description
  • Buffers: shared hit=10,282,923 read=637,802 written=2
3. 1,469.773 11,313.210 ↓ 270,954.0 541,908 1

Nested Loop (cost=651.07..46,056.57 rows=2 width=41) (actual time=4,959.509..11,313.210 rows=541,908 loops=1)

  • Output: woh.work_order_id, sr.description
  • Buffers: shared hit=5,885,772 read=191,169
4. 645.757 1,372.107 ↓ 356.3 1,210,190 1

Nested Loop (cost=650.50..34,762.96 rows=3,397 width=41) (actual time=160.443..1,372.107 rows=1,210,190 loops=1)

  • Output: wohsrl.work_order_history_id, sr.description
  • Buffers: shared hit=33 read=19,445
5. 0.075 0.075 ↑ 1.0 1 1

Index Scan using status_reason_organization_id_idx on public.status_reason sr (cost=0.28..38.86 rows=1 width=37) (actual time=0.049..0.075 rows=1 loops=1)

  • Output: sr.product_id, sr.organization_id, sr.subdivision_id, sr.status_reason_id, sr.description, sr.order_id, sr.is_active, sr.last_update_user_id, sr.last_update_timestamp, sr.create_user_id, sr.create_timestamp, sr.work_order_status_id, sr.extension_data
  • Index Cond: (sr.organization_id = 3)
  • Filter: ((sr.description)::text = 'No Risk Found'::text)
  • Rows Removed by Filter: 56
  • Buffers: shared hit=23
6. 568.726 726.275 ↓ 40.8 1,210,190 1

Bitmap Heap Scan on public.work_order_history_status_reason_link wohsrl (cost=650.23..34,427.58 rows=29,651 width=16) (actual time=160.389..726.275 rows=1,210,190 loops=1)

  • Output: wohsrl.work_order_history_id, wohsrl.status_reason_id, wohsrl.last_update_user_id, wohsrl.last_update_timestamp, wohsrl.create_user_id, wohsrl.create_timestamp
  • Recheck Cond: (wohsrl.status_reason_id = sr.status_reason_id)
  • Heap Blocks: exact=15,275
  • Buffers: shared hit=10 read=19,445
7. 157.549 157.549 ↓ 40.8 1,210,190 1

Bitmap Index Scan on work_order_history_status_reason_link_status_reason_id_idx (cost=0.00..642.81 rows=29,651 width=0) (actual time=157.549..157.549 rows=1,210,190 loops=1)

  • Index Cond: (wohsrl.status_reason_id = sr.status_reason_id)
  • Buffers: shared hit=1 read=4,179
8. 8,471.330 8,471.330 ↓ 0.0 0 1,210,190

Index Scan using work_order_history_work_order_history_id_idx on public.work_order_history woh (cost=0.56..3.31 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=1,210,190)

  • Output: woh.work_order_history_id, woh.work_order_id, woh.user_id, woh.action, woh.comment, woh.screen_shot_key, woh.export_timestamp, woh.last_update_user_id, woh.last_update_timestamp, woh.create_user_id, woh.create_timestamp, woh.extension_data
  • Index Cond: (woh.work_order_history_id = wohsrl.work_order_history_id)
  • Filter: (((woh.action)::text = 'Approve'::text) AND ((timezone('US/Eastern'::text, timezone('UTC'::text, woh.last_update_timestamp)))::date >= '2020-02-01'::date) AND ((timezone('US/Eastern'::text, timezone('UTC'::text, woh.last_update_timestamp)))::date <= '2020-10-07'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5,885,739 read=171,724
9. 17,882.964 17,882.964 ↓ 0.0 0 541,908

Index Scan using work_order_pkey on public.work_order wo (cost=0.56..8.24 rows=1 width=566) (actual time=0.033..0.033 rows=0 loops=541,908)

  • Output: wo.product_id, wo.organization_id, wo.subdivision_id, wo.work_order_id, wo.work_order_unique_consumer_id, wo.work_order_number, wo.job_type, wo.tech_name, wo.latitude, wo.longitude, wo.work_date, wo.due_date, wo.street_address, wo.state_code, wo.city_name, wo.county_name, wo.zip_code, wo.work_order_status_id, wo.extension_data, wo.last_update_user_id, wo.last_update_timestamp, wo.create_user_id, wo.create_timestamp, wo.display_format
  • Index Cond: (wo.work_order_id = woh.work_order_id)
  • Filter: ((wo.extension_data ->> 'ProfitCenter'::text) = '102 - East MD'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,387,159 read=446,633 written=2
Planning time : 3.730 ms
Execution time : 29,888.389 ms