N1ql Faster query

SQL++
,
1

Query is

> SELECT {t2.reseller.vat_country_code,t2.reseller.uuid,t2.reseller.debtor_id,t2.reseller.status,t2.reseller.payment_status,t2.reseller.business_contact_name,t2.reseller.company_name,t2.reseller.address,t2.reseller.vat_number,t2.reseller.tax_number} AS reseller, ARRAY_AGG({t2.payments , “account”:{t2.account.b2b_reseller_uuid,t2.account.payment_period,t2.account.payment_status,t2.account.taxrate,t2.account.uuid,t2.account.email,t2.account.country,t2.account.status}}) AS accounts FROM ( SELECT t1.reseller,t1.account, ARRAY_AGG({payment.uuid,payment.account_uuid,payment.account_uuid,payment.payment_status,payment.reseller_uuid,payment.account_uuid,“payment”:{payment.payment.txn_id,payment.payment.subscr_id,payment.payment.mc_gross,payment.payment.mc_gross,payment.payment.payment_date}}) AS payments FROM ( SELECT * FROM catalog payment LEFT OUTER JOIN catalog AS reseller ON reseller.uuid = payment.reseller_uuid LEFT OUTER JOIN catalog AS account 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.is_archived = false AND account.is_archived = false AND (reseller.status = “pending_verification” OR reseller.status =“active” OR reseller.status = “pending_debtor_id” ) AND reseller.payment_status = “payment_completed” AND account.payment_status =“payment_completed” AND payment.payment_status = “payment_completed”) AS t1 GROUP BY t1.reseller, t1.account) AS t2 GROUP BY t2.reseller ORDER BY t2.reseller.created_at DESC LIMIT 10 OFFSET 0

And I have Written follwing index

  1. CREATE INDEX idx_reseller ON catalog(uuid,b2b_portal_uuid,(type.account_emails),address,step_count,status,tax_proof_status,address_proof_status,payment_status,debtor_id,vat_number,name,vat_country_code,phone_number,company_name,tax_number,business_contact_name,updatedAt DESC,createdAt DESC) WHERE (table_type = “b2b_reseller”)
    2.CREATE INDEX idx_payment ON catalog(uuid,table_type,account_uuid,payment,payment.txn_id,payment.mc_gross,payment.payment_date) WHERE (table_type = “b2b_payment_transaction”)
    3.CREATE INDEX idx_account ON catalog(uuid,table_type,payment_status,b2b_reseller_uuid,country,taxrate,email) WHERE (table_type = “b2b_account”)

But when i ran this query it takes 15 seconds or above…how to reduce this time?

2

This complex query with multiple joins and aggregates. You should check request profiling and optimize it https://blog.couchbase.com/n1ql-practical-guide-second-edition/