Re: Query optimization problem

On Tue, Jul 20, 2010 at 3:33 PM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Jul 20, 2010 at 11:23 AM, Dimitri Fontaine
>> <dfontaine(at)hi-media(dot)com> wrote:
>>>   JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
>>> - WHERE (d1.ID=234409763) or (d2.ID=234409763)
>>> + WHERE (d2.BasedOn=234409763) or (d2.ID=234409763)
>>
>> 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.
>
> Well what I'm thinking now could have nothing to do with how the code
> works. I'd have to check, but well, it's easier to write this mail and
> get the chance to have you wonder :)
>
> So, the JOIN condition teaches us that d2.BasedOn=d1.ID, and the OP
> would want the planner to derive that (d1.ID=234409763) is the same
> thing as (d2.BasedOn=234409763). I guess it would make sense to produce
> plans with both the writings and pick one based on the costs.
>
> Now, does it make sense to generate this many more plans to analyze in
> the general case, I have no idea about. But given only one join and only
> one WHERE clause where the Equivalent applies…

It seems like deciding which rel to apply the filter condition to
would be a fairly expensive optimization. Perhaps we could recognize
the special case where substituting another member of the equivalence
class allows the qual to be pushed down where it otherwise couldn't
be.

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