Re: Query optimization problem

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…

Regards,
--
dim