Kevin Grittner <kgrittn@xxxxxxxxx> writes: > Willy-Bas Loos <willybas@xxxxxxxxx> wrote: >> I can't understand what is confusing the planner. > Well, it doesn't do exhaustive proofs of whether two queries are > equivalent. If it did, it would still not have come up with a plan > like your second one, because it is not equivalent. Yeah. The short reason why the index was not used in the original query is that the supposedly indexable condition was inside an OR, which made it useless as an index qualification: rows not satisfying that condition at all might yet satisfy the query as a whole. 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. Another point about the proposed transformation is that an OR in WHERE is far from equivalent to a UNION: WHERE ... OR does not result in full de-duplication. You could possibly conclude they were equivalent if the query output columns included a primary key, but that was not the case here. 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. You'd often get a worse plan than you get now. (Perhaps that will change someday, but it's not very high on the priority list.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
