explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Ohy

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,798,456.42..1,798,456.47 rows=1 width=127) (actual rows= loops=)

  • Group Key: (date_part('isoyear'::text, order_line_item.order_date)), (date_part('week'::text, order_line_item.order_date)), order_line_item.partner, member.member_state, (substr(member.member_zip, 1, 5)), (CASE WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 0.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 25.0)) THEN '[0,25)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 25.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 50.0)) THEN '[0,50)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 50.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 75.0)) THEN '[50,75)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 75.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric <= 100.0)) THEN '[75,100]'::text ELSE NULL::text END), member_1.member_state, (substr(member_1.member_zip, 1, 5))
2. 0.000 0.000 ↓ 0.0

Sort (cost=1,798,456.42..1,798,456.43 rows=1 width=119) (actual rows= loops=)

  • Sort Key: (date_part('isoyear'::text, order_line_item.order_date)), (date_part('week'::text, order_line_item.order_date)), order_line_item.partner, member.member_state, (substr(member.member_zip, 1, 5)), (CASE WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 0.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 25.0)) THEN '[0,25)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 25.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 50.0)) THEN '[0,50)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 50.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric < 75.0)) THEN '[50,75)'::text WHEN (((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric >= 75.0) AND ((NULLIF(a.mdr_title_i_percent__c, ''::text))::numeric <= 100.0)) THEN '[75,100]'::text ELSE NULL::text END), member_1.member_state, (substr(member_1.member_zip, 1, 5))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,004.01..1,798,456.41 rows=1 width=119) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Gather (cost=1,003.44..1,798,453.72 rows=1 width=74) (actual rows= loops=)

  • Workers Planned: 2
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.44..1,797,453.62 rows=1 width=74) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.00..1,797,451.20 rows=1 width=72) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,572,506.78 rows=1 width=40) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on member member_1 (cost=0.00..824,114.94 rows=350 width=14) (actual rows= loops=)

  • Filter: ((receiver_classification <> 'Out of network, out of list (open destination)'::text) AND (ritype = ANY ('{RT_UNIV,RT_JC,RT_TECH,RT_CA_CO,RT_UNIV_PR,RT_CAREER,RT_JC_PR}'::text[])))
9. 0.000 0.000 ↓ 0.0

Index Scan using order_line_item_receiving_member_id_idx on order_line_item (cost=0.57..2,138.25 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (receiving_member_id = member_1.member_pkid)
  • Filter: (is_transaction AND (NOT usv) AND (master_product_name = 'Transcript'::text) AND (source_system = ANY ('{ALPHA,DELTA,RMS}'::text[])) AND (date_part('isoyear'::text, order_date) >= '2017'::double precision) AND (date_part('week'::text, order_date) >= '2'::double precision) AND (date_part('week'::text, order_date) <= '19'::double precision))
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.43..224,944.41 rows=1 width=36) (actual rows= loops=)

  • Hash Cond: (a.id = m.salesforce_account_id)
11. 0.000 0.000 ↓ 0.0

Foreign Scan on account_fdw a (cost=0.00..217,574.40 rows=1,964,684 width=64) (actual rows= loops=)

  • Foreign File: /array/pgsql/prod/fdw_files/salesforce/account.csv
  • Foreign File Size: 172892176 b
12. 0.000 0.000 ↓ 0.0

Hash (cost=2.42..2.42 rows=1 width=23) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using member_member_pkid_idx on member m (cost=0.44..2.42 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (order_line_item.sending_member_id = member_pkid)
14. 0.000 0.000 ↓ 0.0

Index Scan using member_member_pkid_idx on member (cost=0.44..2.42 rows=1 width=14) (actual rows= loops=)

  • Index Cond: (member_pkid = order_line_item.sending_member_id)
  • Filter: (member_type = 'HS'::text)
15. 0.000 0.000 ↓ 0.0

Index Scan using order_request_uor_order_id_idx on order_request (cost=0.57..2.68 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (uor_order_id = order_line_item.uor_order_id)
  • Filter: ((NOT order_test) AND order_valid)