On Sun, Sep 14, 2014 at 3:23 PM, Kevin Grittner <kgrittn@ymail.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@sss.pgh.pa.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.