explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aOmv

Settings
# exclusive inclusive rows x rows loops node
1. 3.257 1,899.566 ↑ 1.0 1 1

Aggregate (cost=184,268.79..184,268.80 rows=1 width=8) (actual time=1,899.565..1,899.566 rows=1 loops=1)

  • Output: count(*)
2.          

CTE co

3. 867.449 1,667.843 ↑ 1.3 40,258 1

Bitmap Heap Scan on terminal.customer_order (cost=86,101.55..182,159.41 rows=52,718 width=2,217) (actual time=932.684..1,667.843 rows=40,258 loops=1)

  • Output: customer_order.order_number, customer_order.crm_order_id, customer_order.order_reviewed_date, customer_order.order_comments, customer_order.order_status, customer_order.order_quote_id, customer_order.order_source, customer_order.order_sub_status, customer_order.order_sub_source, customer_order.order_submission_date, customer_order.order_type, customer_order.order_reviewer_ucid, customer_order.order_reviewer_username, customer_order.account_name, customer_order.account_number, customer_order.account_ucid, customer_order.csc_user_key, customer_order.csc_user_name, customer_order.customer_reference_number, customer_order.customer_followup_flag, customer_order.ecp_reference_number, customer_order.currency_code, customer_order.booked_date, customer_order.cancelled_date, customer_order.future_booking_date, customer_order.po_bearing_customer_flag, customer_order.service_request_number, customer_order.activity_rollup_status, customer_order.expedite_flag, customer_order.expedite_rollup_status, customer_order.macd_flag, customer_order.hold_flag, customer_order.pii_hold_flag, customer_order.pii_rollup_status, customer_order.tibco_integration_status, customer_order.attachment_info, customer_order.additional_info, customer_order.contact_info, customer_order.note_info, customer_order.created_by, customer_order.created_date, customer_order.last_updated_date, customer_order.last_updated_by, customer_order.deleted_flag, customer_order.ignore_updates_flag, customer_order.etl_created_by, customer_order.etl_data_source, customer_order.etl_created_date, customer_order.etl_last_updated_by, customer_order.etl_last_updated_date, customer_order.row_modification_counter, customer_order.ibx_rollup, customer_order.product_rollup, customer_order.cage_rollup, customer_order.additional_rollup, customer_order.quote_flag, customer_order.order_completion_flag, customer_order.order_in_progress_flag, customer_order.crm_processed_flag, customer_order.service_request_number_csc, customer_order.contact_rollup, customer_order.notification_contact_last_updated_date, customer_order.service_request_status_rollup, customer_order.quote_desk_specialist
  • Recheck Cond: ((customer_order.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[])) AND (customer_order.order_submission_date >= to_date('2019-06-27'::text, 'YYYY-MM-DD'::text)) AND (customer_order.order_submission_date <= to_date('2020-06-28'::text, 'YYYY-MM-DD'::text)))
  • Heap Blocks: exact=497,132
4. 37.647 800.394 ↓ 0.0 0 1

BitmapAnd (cost=86,101.55..86,101.55 rows=52,718 width=0) (actual time=800.394..800.394 rows=0 loops=1)

5. 187.966 187.966 ↓ 8.6 1,000,810 1

Bitmap Index Scan on customer_order_account_number_idx (cost=0.00..2,087.86 rows=116,695 width=0) (actual time=187.966..187.966 rows=1,000,810 loops=1)

  • Index Cond: (customer_order.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[]))
6. 574.781 574.781 ↑ 1.3 2,341,535 1

Bitmap Index Scan on customer_order_order_submission_date_idx (cost=0.00..83,987.08 rows=2,994,264 width=0) (actual time=574.781..574.781 rows=2,341,535 loops=1)

  • Index Cond: ((customer_order.order_submission_date >= to_date('2019-06-27'::text, 'YYYY-MM-DD'::text)) AND (customer_order.order_submission_date <= to_date('2020-06-28'::text, 'YYYY-MM-DD'::text)))
7. 1,896.309 1,896.309 ↓ 147.2 38,559 1

CTE Scan on co (cost=0.00..2,108.72 rows=262 width=0) (actual time=932.705..1,896.309 rows=38,559 loops=1)

  • Output: co.order_number, co.crm_order_id, co.order_reviewed_date, co.order_comments, co.order_status, co.order_quote_id, co.order_source, co.order_sub_status, co.order_sub_source, co.order_submission_date, co.order_type, co.order_reviewer_ucid, co.order_reviewer_username, co.account_name, co.account_number, co.account_ucid, co.csc_user_key, co.csc_user_name, co.customer_reference_number, co.customer_followup_flag, co.ecp_reference_number, co.currency_code, co.booked_date, co.cancelled_date, co.future_booking_date, co.po_bearing_customer_flag, co.service_request_number, co.activity_rollup_status, co.expedite_flag, co.expedite_rollup_status, co.macd_flag, co.hold_flag, co.pii_hold_flag, co.pii_rollup_status, co.tibco_integration_status, co.attachment_info, co.additional_info, co.contact_info, co.note_info, co.created_by, co.created_date, co.last_updated_date, co.last_updated_by, co.deleted_flag, co.ignore_updates_flag, co.etl_created_by, co.etl_data_source, co.etl_created_date, co.etl_last_updated_by, co.etl_last_updated_date, co.row_modification_counter, co.ibx_rollup, co.product_rollup, co.cage_rollup, co.additional_rollup, co.quote_flag, co.order_completion_flag, co.order_in_progress_flag, co.crm_processed_flag, co.service_request_number_csc, co.contact_rollup, co.notification_contact_last_updated_date, co.service_request_status_rollup, co.quote_desk_specialist
  • Filter: (((co.contact_info IS NOT NULL) AND (((co.contact_info -> 'orderingContacts'::text) @> '{"userName": "ABDULHAMEEDS@GOOGLE.COM"}'::jsonb) OR ((co.contact_info -> 'orderingContacts'::text) @> '{"contactUCId": "734998"}'::jsonb) 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: 1,699
Planning time : 1.978 ms
Execution time : 1,915.873 ms