From: Tom Lane <tgl@sss.pgh.pa.us>
To: Václav Ovsík <vaclav.ovsik@i.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Date: Wed, 13 Apr 2011 12:24:06 -0400
Message-ID: <6361.1302711846@sss.pgh.pa.us> (raw)
In-Reply-To: <20110413082139.GB24050@bobek.localdomain>
References: <20110412232343.GA15966@bobek.localdomain>
<20568.1302655935@sss.pgh.pa.us>
<20110413082139.GB24050@bobek.localdomain>
=?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
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: tgl@sss.pgh.pa.us, vaclav.ovsik@i.cz
Subject: Re: poor execution plan because column dependence
In-Reply-To: <6361.1302711846@sss.pgh.pa.us>
* 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