explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fj5Y : Optimization for: Optimization for: plan #El9d; plan #8pz

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.801 106.555 ↑ 1.0 1 1

Aggregate (cost=25,645.67..25,645.68 rows=1 width=8) (actual time=106.555..106.555 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=21,349
2.          

CTE co

3. 9.597 48.325 ↓ 34.3 12,827 1

Nested Loop (cost=0.85..25,628.83 rows=374 width=969) (actual time=0.096..48.325 rows=12,827 loops=1)

  • Output: o.order_submission_date, o.contact_info, o.contact_rollup, o.ibx_rollup, o.cage_rollup
  • Buffers: shared hit=21,349
4. 0.501 0.501 ↑ 1.0 43 1

Index Only Scan using unique_account_number on terminal.account a (cost=0.42..132.78 rows=43 width=7) (actual time=0.068..0.501 rows=43 loops=1)

  • Output: a.account_number
  • Index Cond: (a.account_number = ANY ('{135565,110546,561902,350705,110864,109958,100330,309282,573890,3517,132575,4016,7176,502269,136420,576480,561266,553315,575881,586807,576120,558470,548076,350722,350714,350709,300134,301446,309159,134898,132364,111999,350719,578813,570223,557381,300940,330038,330057,323449,323230,156850,556661}'::text[]))
  • Heap Fetches: 18
  • Buffers: shared hit=161
5. 38.227 38.227 ↓ 1.9 298 43

Index Scan using customer_order_account_number_idx on terminal.customer_order o (cost=0.43..591.36 rows=157 width=975) (actual time=0.014..0.889 rows=298 loops=43)

  • Output: o.order_number, o.crm_order_id, o.order_reviewed_date, o.order_comments, o.order_status, o.order_quote_id, o.order_source, o.order_sub_status, o.order_sub_source, o.order_submission_date, o.order_type, o.order_reviewer_ucid, o.order_reviewer_username, o.account_name, o.account_number, o.account_ucid, o.csc_user_key, o.csc_user_name, o.customer_reference_number, o.customer_followup_flag, o.ecp_reference_number, o.currency_code, o.booked_date, o.cancelled_date, o.future_booking_date, o.po_bearing_customer_flag, o.service_request_number, o.activity_rollup_status, o.expedite_flag, o.expedite_rollup_status, o.macd_flag, o.hold_flag, o.pii_hold_flag, o.pii_rollup_status, o.tibco_integration_status, o.attachment_info, o.additional_info, o.contact_info, o.note_info, o.created_by, o.created_date, o.last_updated_date, o.last_updated_by, o.deleted_flag, o.ignore_updates_flag, o.etl_created_by, o.etl_data_source, o.etl_created_date, o.etl_last_updated_by, o.etl_last_updated_date, o.row_modification_counter, o.ibx_rollup, o.product_rollup, o.cage_rollup, o.additional_rollup, o.quote_flag, o.order_completion_flag, o.order_in_progress_flag, o.crm_processed_flag, o.service_request_number_csc, o.contact_rollup, o.notification_contact_last_updated_date, o.service_request_status_rollup, o.quote_desk_specialist
  • Index Cond: (o.account_number = a.account_number)
  • Filter: ((o.order_submission_date >= to_date('2019-06-27'::text, 'YYYY-MM-DD'::text)) AND (o.order_submission_date <= to_date('2020-06-28'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 193
  • Buffers: shared hit=21,188
6. 104.754 104.754 ↓ 2,525.2 12,626 1

CTE Scan on co (cost=0.00..16.83 rows=5 width=0) (actual time=0.107..104.754 rows=12,626 loops=1)

  • Output: co.order_submission_date, co.contact_info, co.contact_rollup, co.ibx_rollup, co.cage_rollup
  • Filter: (((co.contact_info IS NOT NULL) AND ((((co.contact_info -> 'orderingContacts'::text) ->> 'userName'::text) = 'ABDULHAMEEDS@GOOGLE.COM'::text) OR (((co.contact_info -> 'orderingContacts'::text) ->> 'contactUCId'::text) = '734998'::text) OR ((co.contact_rollup -> 'technicalContacts'::text) @> '[{"contactUCId": "734998"}]'::jsonb))) OR ((co.ibx_rollup IS NOT NULL) AND (co.ibx_rollup ?| '{MU1,AM4,BG1,MI1,SV1,ZH5,HE7,SK2,AT1,PA4,FR2,MD2,RJ2}'::text[])) OR ((co.cage_rollup IS NOT NULL) AND (co.cage_rollup ?| '{AM3:01:041,004,AM3:01:040,904,AM3:01:011,201,AM5:01:000Z3A,AM5:01:000Z3C,AM6:01:000Z3D,MU3:01:111,105,LS1:01:001,020,LS1:01:001,023,DC2:01:002,050,DC2:01:001,030,DC6:01:061,160,DC5:01:052,225,SE3:05:050,480,SE3:07:070,200,SE2:05:050,010,SE2:05:050,055,WA1:03:003C01,WA1:03:003S01,SV5:01:052,670,SV5:01:051,785,SV5:01:052,645,SV5:01:051,710,SV8:02:082,110,SV8:01:081,015,SV8:02:082,140,PH1:09:019,870,LD5:01:001MC2,LD5:0G:00GS06,LD4:0G:V00GC01-11,LD6:01:1MC323,LD8:02:02MC2Z,LD8:04:02MMR4,DC10:01:V004340-1,DC10:01:000,104,DC10:01:004,310,DC10:01:004,320,ZH2:03:0ROOM2,ZH2:03:V0ROOM1-1,ZH4:02:Z01C01,NY8:15:015,100,NY9:15:ZV01515-496,DA2:02:002,120,DA1:01:000,280,DA1:R1:V000372-2-611,DA1:01:000,372,DA6:03:030,110,DA6:04:641,500,SK1:04:000Z5H,SK1:04:000Z1A,LA1:07:000,785,LA1:07:000,535,LA1:07:000,835,LA1:06:000,030,AT2:05:050,000,PA2:0G:Z-656,AT3:06:060,053,PA3:0G:00GMC2,FR5:03:VM314S1-23,FR5:03:VM309S1-10,FR5:03:VM314S1-22,FR5:03:VM309S1-11,FR4:0G:030,231,FR4:02:FLX305,FR6:01:103,031,FR6:01:101,051,FR6:01:STR102,FR6:01:101,010,FR6:01:101,014,FR6:01:101,011,CH2:06:003,205,CH2:06:003,130,CH2:06:001,050,CH1:05:000,733,PAA:01:VPOP001-79,TR1:07:070,700,SP2:01:023,525,SP2:01:022,240,TR2:04:044,115,TR2:03:034,415,SP4:01:414,220,SP4:01:416,265}'::text[])))
  • Rows Removed by Filter: 201
  • Buffers: shared hit=21,349
Planning time : 0.810 ms
Execution time : 107.571 ms