Re: Query optimization problem

On Tue, Jul 20, 2010 at 11:23 AM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> All that having been said, I think the issue here is that the query
>> planner isn't inferring that d1.ID=<some constant> implies d2.ID=<some
>> constant>, even though there's a join clause d1.ID=d2.ID.
>
> I think that's what the Equivalence Classes are for. Or at least that's
> what they do in my head, not forcibly in the code.
>
> The specific diff between the two queries is :
>
>   JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
> - WHERE (d1.ID=234409763) or (d2.ID=234409763)
> + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763)
>
> So the OP would appreciate that the planner is able to consider applying
> the restriction on d2.BasedOn rather than d1.ID given that d2.BasedOn is
> the same thing as d1.ID, from the JOIN.
>
> I have no idea if Equivalence Classes are where to look for this, and if
> they're meant to extend up to there, and if that's something possible or
> wise to implement, though.

I was thinking of the equivalence class machinery as well. I think
the OR clause may be the problem. If you just had d1.ID=constant, I
think it would infer that d1.ID, d2.BasedOn, and the constant formed
an equivalence class. But here you obviously can't smash the constant
into the equivalence class, and I think the planner's not smart enough
to consider other ways of applying an equivalent qual. In fact, I
have some recollection that Tom has explicitly rejected adding support
for this in the past, on the grounds that the computation would be too
expensive for the number of queries it would help. Still, it seems to
keep coming up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company