Query time increases when logic changed

SQL++
,
1

i have query :slight_smile:
SELECT t2.reseller , ARRAY_AGG({t2.payments, t2.account}) AS accounts FROM ( SELECT t1.reseller,t1.account, ARRAY_AGG(payment) AS payments FROM ( SELECT * FROM catalog payment LEFT OUTER JOIN catalog AS reseller USE HASH(BUILD) ON reseller.uuid = payment.reseller_uuid LEFT OUTER JOIN catalog AS account USE HASH(BUILD) ON account.uuid = payment.account_uuid WHERE payment.table_type = “b2b_payment_transaction” AND reseller.table_type = “b2b_reseller” AND account.table_type = “b2b_account” AND (reseller.payment_status = “payment_completed” OR reseller.payment_status = “admin_approved”) AND (account.payment_status = “payment_completed” OR account.payment_status = “admin_approved”) AND ( payment.payment_status = “processed” OR payment.payment_status = “invoiced” ) AND reseller.is_archived = false AND account.is_archived = false ) AS t1 GROUP BY t1.reseller, t1.account) AS t2 GROUP BY t2.reseller

this works fine .Take more or less 100ms .
But when i change the logic for example
( payment.payment_status = “payment_completed” OR payment.payment_status = “admin_approved” ) it took 12 second.
Is that a problem with my index or other issue ?

2

May be index. enable profile and check #InItems,#OutTimes, timings for each phase. If need help post profile . Use IN clause much readable

payment_status IN [“xx”,“yy”]

Also project only required fields from inner instead of * also see if u can use covering indexes.

SELECT t2.reseller , ARRAY_AGG({t2.payments, t2.account}) AS accounts
FROM ( SELECT t1.reseller,t1.account, ARRAY_AGG(payment) AS payments
       FROM ( SELECT *
              FROM catalog AS payment
              LEFT OUTER JOIN catalog AS reseller USE HASH(BUILD) ON reseller.uuid = payment.reseller_uuid
              LEFT OUTER JOIN catalog AS account USE HASH(BUILD) ON account.uuid = payment.account_uuid
              WHERE payment.table_type = "b2b_payment_transaction"
                   AND reseller.table_type = "b2b_reseller" AND reseller.is_archived = false
                   AND account.table_type = "b2b_account"  AND account.is_archived = false
                   AND reseller.payment_status IN ["payment_completed","admin_approved"]
                   AND account.payment_status IN ["payment_completed","admin_approved"]
                   AND payment.payment_status IN ["processed","invoiced"]
            ) AS t1
      GROUP BY t1.reseller, t1.account
 ) AS t2
GROUP BY t2.reseller;

CREATE INDEX pix1 ON catalog(payment_status,reseller_uuid) WHERE table_type = "b2b_payment_transaction";
CREATE INDEX rix1 ON catalog(payment_status, is_archived , uuid) WHERE table_type = "b2b_reseller";
CREATE INDEX aix1 ON catalog(payment_status, is_archived , uuid) WHERE table_type = "b2b_account";