Message-ID: <4E9D1A25.5050607@catalyst.net.nz>
Date: Tue, 18 Oct 2011 19:18:13 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Robins Tharakan <robins.tharakan@comodo.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Bad plan by Planner (Already resolved?)
References: <4E9BCB1F.9060208@comodo.com>
In-Reply-To: <4E9BCB1F.9060208@comodo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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