From: Robins Tharakan <robins.tharakan@comodo.com>
To: pgsql-performance@postgresql.org
Subject: Bad plan by Planner (Already resolved?)
Date: Mon, 17 Oct 2011 11:58:47 +0530
Message-ID: <4E9BCB1F.9060208@comodo.com> (raw)
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
--
Robins Tharakan
Attachments:
smime.p7s (application/pkcs7-signature, 6.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robins.tharakan@comodo.com
Subject: Re: Bad plan by Planner (Already resolved?)
In-Reply-To: <4E9BCB1F.9060208@comodo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox