To: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
cc: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence 
In-reply-to: <20110413082139.GB24050@bobek.localdomain> 
References: <20110412232343.GA15966@bobek.localdomain>
	<20568.1302655935@sss.pgh.pa.us>
	<20110413082139.GB24050@bobek.localdomain>
Comments: In-reply-to =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
	message dated "Wed, 13 Apr 2011 10:21:39 +0200"
Date: Wed, 13 Apr 2011 12:24:06 -0400
Message-ID: <6361.1302711846@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz> writes:
> On Tue, Apr 12, 2011 at 08:52:15PM -0400, Tom Lane wrote:
>> ... If you can change it, try replacing main.EffectiveId = main.id
>> with the underlying function, eg if they're integers use
>> int4eq(main.EffectiveId, main.id).  This will bypass the overoptimistic
>> estimator for the "=" operator and get you a default selectivity
>> estimate of (IIRC) 0.3333.  Which is still off, but only by 3x not 200x,
>> and that should be close enough to get a decent plan.

> Great idea!

> Interesting the original index tickets5 is still used for
> int4eq(main.effectiveid, main.id), no need to build a different.

Well, no, it won't be.  This hack is entirely dependent on the fact that
the optimizer mostly works with operator expressions, and is blind to
the fact that the underlying functions are really the same thing.
(Which is something I'd like to see fixed someday, but in the meantime
it gives you an escape hatch.)  If you use the int4eq() construct in a
context where you'd like to see it transformed into an index qual, it
won't be.  For this particular case that doesn't matter because there's
no use in using an index for that clause anyway.  But you'll need to be
very careful that your changes in the query generator don't result in
using int4eq() in any contexts other than the "main.EffectiveId=main.id"
check.

			regards, tom lane