On 17/10/11 19:28, Robins Tharakan wrote:
Hi,
I stumbled upon a situation where the planner comes with a bad query
plan, but I wanted to mention upfront that I'm using a dated PG
version and I already see an update which mentions about improving
planner performance. I just wanted to check if this issue is already
resolved, and if so, which version should I be eyeing.
My PG Version: 8.4.7
Probably solved in: 8.4.8 / 9.0.4 ?
Issue: It seems that the planner is unable to flatten the IN sub-query
causing the planner to take a bad plan and take ages (>2500 seconds)
and expects to give a 100 million row output, where in-fact it should
get a six row output. The same IN query, when flattened, PG gives the
correct result in a fraction of a second.
Do let me know if this is a new case. I could try to give you the
EXPLAIN ANALYSE outputs / approximate table sizes if required.
EXISTING QUERY:
SELECT field_b FROM large_table_a
JOIN large_table_b USING (field_b)
WHERE field_a IN (SELECT large_table_b.field_a
FROM large_table_b WHERE field_b = 2673056)
RECOMMENDED QUERY:
SELECT s1.field_b FROM large_table_a
JOIN large_table_b s1 USING (field_b)
JOIN large_table_b s2 ON s1.field_a = s2.field_a
WHERE s2.field_b = 2673056
Poor plans being generated for the subquery variant above were
specifically targeted in 8.4.9. It may be that you don't need the
workaround in that (or corresponding later) versions - 9.0.5, 9.1.0.
Regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
