Query performance !
Hi Experts,
The attached query is performing slow, this needs to be optimized to
improve the performance.
Could you help me with query rewrite (or) on new indexes to be created 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
Re: Performance of lateral join
>Easy first question: is the temp table analyzed before being used in a > join ? No, I haven't done that. Today, I tried to run ANALYZE records_to_filter_on; on the same sample data set (3.75 million rows) before the join, and it did not seem to make much of a difference in terms of time (new output of EXPLAIN ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to me). Not sure if it is relevant, but I did some experimentation with smaller samples, and for those, there was a significant speedup. Could there be some size threshold on the temp table after which running ANALYZE does not yield any speedup? >I think the attachment is missing. Added now. Simen explain_analyze.json Description: explain_analyze.json pg_settings.conf Description: pg_settings.conf
Re: Query performance !
Please don't cross post to multiple lists like this. Cc: [email protected], [email protected], [email protected], [email protected] If you're hoping for help on the -performance list, see this page and send the "explain analyze" for this query. https://wiki.postgresql.org/wiki/Slow_Query_Questions On Tue, Jul 27, 2021 at 05:29:19AM +0530, kenny a wrote: > Hi Experts, > > The attached query is performing slow, this needs to be optimized to > improve the performance. > > Could you help me with query rewrite (or) on new indexes to be created to > improve the performance? > > Thanks a ton in advance for your support.
