Hi Experts,
The attached query is performing slow, this needs to be optimized to improve the performance.
Thanks a ton in advance for your support.
SELECT tab2.rulename,
tab2.totalexecuted,
tab2.uniqueorder,
tab1.des description,
tab2.max,
tab1.ruletype,
tab2.uniqueorder pertange
FROM (SELECT re.rule_name ruleName,
Count (*) totalExecuted,
Count (DISTINCT re.order_id) uniqueOrder,
Max (re.id)
FROM rule_execution re
WHERE ? = ?
AND re.status = ?
AND re. type IN ( ?, ? )
AND re.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re.rule_name) tab2
INNER JOIN (SELECT re2.rule_name,
Max (re2.rule_description) des,
Max (re2. type) ruleType
FROM (SELECT re4.rule_name,
Max (re4.created_date)
FROM sample.rule_execution re4
WHERE ? = ?
AND re4.status = ?
AND re4. type IN ( ?, ? )
AND re4.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re4.rule_name) re1
INNER JOIN rule_execution re2
ON re2.rule_name = re1.rule_name
AND re2.created_date = re1. max
GROUP BY re2.rule_name) tab1
ON tab1.rule_name = tab2.rulename
ORDER BY totalexecuted DESC,
rulename ASC
LIMIT ?
currrent indexes on rule_execution table :
sample_rule_execution_upper_sample_id_idx
sample_rule_execution_sample_id_idx
sample_rule_execution_order_id_idx
rule_pkey
rule_execution_migration_unique
