Re: 2 left joins causes seqscan

On Sun, Sep 14, 2014 at 3:23 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> The fact that the
> first query was complex enough that *you* weren't able to
> accurately optimize it better before posting is pretty good
> evidence that it's moving into the realm of "expensive to
> optimize".
>
>
Touche
BTW i don't mean any offense. I don't expect for anyone to waste their time
on cases that aren't worth the while. I just brought this up because i want
to help make the planner even more awesome. I believe that planner hints
are a bad thing, because the planner should be able to solve it
automatically. If it doesn't, users and developers should talk to each
other so that the planner (and/or knowledge how to use it) keeps improving.

On Sun, Sep 14, 2014 at 3:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The
> planner does have some ability to use indexes when every arm of the
> OR includes an indexable condition on the same table, but that was
> not the case here.
>

It wasn't? I think that it was. I guess that this is the core of my
question.
But, in a way you are right. The planner probably sees it as a different
table, because it is a different join, even though it is the same table.
Ok, i understand now that it is probably too much to ask from the planner.
Because OR negates the effect of the qualifications in the WHERE clause and
both joins might be the same table, but that is -arguably- a corner case.
Thanks both of you.

>
> In any case, the planner includes
> no logic that could transform OR into UNION, and I'd be pretty
> hesitant to add any even if the transformation were formally correct,
> because the planner has no ability to optimize UNION meaningfully.
>

No, i didn't mean to say that the planner should do that, just that getting
the data in 2 queries (and appending with union (all)) which was faster
than the 1 query.

Cheers,
--
Willy-Bas Loos