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
- CREATE INDEX
idx_reseller
ONcatalog
(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 INDEXidx_payment
ONcatalog
(uuid
,table_type
,account_uuid
,payment
,payment.txn_id
,payment.mc_gross
,payment.payment_date
) WHERE (table_type
= “b2b_payment_transaction”)
3.CREATE INDEXidx_account
ONcatalog
(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?