SELECT ord.id              AS ord_id,
       oli.id              AS oli_id,
       ord.dealer_code__c  AS ord_dealer_code__c,
       ord.recordtypeid    AS ord_recordtypeid,
       ord.order_number__c AS ord_order_number__c,
       ord.status          AS ord_status,
       ord.opportunityid   AS ord_opportunityid,
       ord.sfid            AS ord_sfid,
       ord.pricebook2id    AS ord_pricebook2id,
       cont.opportunity__c AS cont_opportunity__c,
       cont.sfid           AS cont_sfid,
       opp.sfid            AS opp_sfid,
       opp.recordtypeid    AS opp_recordtypeid,
       opp.pricebook2id    AS opp_pricebook2id,
       accban.sfid         AS accban_sfid,
       accban.ban__c       AS accban_ban__c,
       usr.sfid            AS usr_sfid
FROM   fullsbxsalesforce.order ord
       LEFT JOIN fullsbxsalesforce.contract cont
              ON ord.contractid = cont.sfid
       LEFT JOIN fullsbxsalesforce.opportunity opp
              ON cont.opportunity__c = opp.sfid
       LEFT JOIN fullsbxsalesforce.user usr
              ON ( ord.dealer_code__c = usr.dealer_code_bd__c
                    OR ord.dealer_code__c = usr.dealer_code_co_sell__c
                    OR ord.dealer_code__c = usr.rep_dealer_code__c
                    OR ord.dealer_code__c = usr.dealer_code_secondary__c )
       LEFT JOIN fullsbxsalesforce.account_ban_tax_id__c accban
              ON ord.ban_number__c = accban.ban__c
       LEFT JOIN fullsbxsalesforce.orderitem oli
              ON oli.sfid = (SELECT sfid
                             FROM   fullsbxsalesforce.orderitem oli
                             WHERE  ord.sfid = oli.orderid
                             LIMIT  1)
WHERE  ord.recordtypeid = 'specificid'
       AND ( ord.status IN ( 'Submitted', 'Pending EIP eSignature',
                             'Partially Shipped',
                                                 'In-Fulfilment Queue',
                             'Locked', 'Pending Approval', 'Resubmitted',
                             'Pending Order',
                             'Pending Customer Completion', 'Backorder', 'Draft'
                             ,
                                   'Credit Card Declined',
                             'External System', 'Shipped/Pending Activation',
                                   'Shipped/Partially Activated'
                                                 , 'SAP Order Does not exist',
                             'Approved', 'Submit Failed for an Easy Order',
                                   'Pending e-Signature',
                                                 'e-Signature Complete',
                             'Credit Card Decline (Hold)', 'Pre-Order',
                             'In Shipping Queue'
                               )
              OR ( ord.status = 'Shipped'
                   AND ord.hasmsisdn__c = false
                   AND ord.last_status_change__c >= CURRENT_DATE -
                                                    INTERVAL '7' day ) );

Nested Loop Left Join  (cost=4283.25..5032.80 rows=2 width=256)
   Join Filter: (((ord.dealer_code__c)::text = (usr.dealer_code_bd__c)::text) OR ((ord.dealer_code__c)::text = (usr.dealer_code_co_sell__c)::text) OR ((ord.dealer_code__c)::text = (usr.rep_dealer_code__c)::text) OR ((ord.dealer_code__c)::text = (usr.dealer_code_secondary__c)::text))
   ->  Nested Loop Left Join  (cost=4283.25..4298.93 rows=1 width=237)
         ->  Nested Loop Left Join  (cost=0.34..12.01 rows=1 width=233)
               ->  Nested Loop Left Join  (cost=0.25..7.92 rows=1 width=215)
                     ->  Nested Loop Left Join  (cost=0.17..6.99 rows=1 width=158)
                           ->  Index Scan using hc_idx_order_recordtypeid on "order" ord  (cost=0.08..2.90 rows=1 width=139)
                                 Index Cond: ((recordtypeid)::text = 'specificid'::text)
                                 Filter: (((status)::text = ANY ('{Submitted,"Pending EIP eSignature","Partially Shipped","In-Fulfilment Queue",Locked,"Pending Approval",Resubmitted,"Pending Order","Pending Customer Completion",Backorder,Draft,"Credit Card Declined","External System","Shipped/Pending Activation","Shipped/Partially Activated","SAP Order Does not exist",Approved,"Submit Failed for an Easy Order","Pending e-Signature","e-Signature Complete","Credit Card Decline (Hold)",Pre-Order,"In Shipping Queue"}'::text[])) OR (((status)::text = 'Shipped'::text) AND (NOT hasmsisdn__c) AND (last_status_change__c >= (('now'::cstring)::date - '7 days'::interval day))))
                           ->  Index Scan using hcu_idx_contract_sfid on contract cont  (cost=0.08..4.09 rows=1 width=38)
                                 Index Cond: ((ord.contractid)::text = (sfid)::text)
                     ->  Index Scan using hcu_idx_opportunity_sfid on opportunity opp  (cost=0.08..0.93 rows=1 width=57)
                           Index Cond: ((cont.opportunity__c)::text = (sfid)::text)
               ->  Index Scan using hcu_idx_account_ban_tax_id__c_ban__c on account_ban_tax_id__c accban  (cost=0.08..4.09 rows=1 width=28)
                     Index Cond: ((ord.ban_number__c)::text = (ban__c)::text)
         ->  Index Scan using hcu_idx_orderitem_sfid on orderitem oli  (cost=4282.91..4286.92 rows=1 width=23)
               Index Cond: ((sfid)::text = ((SubPlan 1))::text)
               SubPlan 1
                 ->  Limit  (cost=0.00..4282.83 rows=1 width=19)
                       ->  Seq Scan on orderitem oli_1  (cost=0.00..25696.96 rows=6 width=19)
                             Filter: ((ord.sfid)::text = (orderid)::text)
   ->  Seq Scan on "user" usr  (cost=0.00..555.20 rows=35733 width=74)
(22 rows)