Re: BUG #5645: Query Optimizer fails when it encounters an unsatisfiable part of a query

"Daniel Wagner" <dtw30(at)cam(dot)ac(dot)uk> writes:
> I posted this earlier on the general mailing list and received no reply. I
> assume I found a genuine bug:
> "select * from kvstore where deviceid = 7 AND (locid >= 1410929 AND locid <=
> 1690468) OR (locid = 1690469 and locid <= 1690468)"

No, you're just expecting the optimizer to spend much more effort than
it actually does on recognizing poorly-written queries. While there is
code in there that could prove that (locid = 1690469 and locid <= 1690468)
is constant false, we don't apply that code to sub-branches of OR
conditions; it's too expensive and the probability of a win is too
small.

> Now, the Postgres optimizer seems to believe that a sequential scan of 16
> million rows is the right way of approaching this query, despite having
> accurate statistics (I ran VACUUM ANALYZE before to ensure everything is
> up-to-date).

Well, you haven't shown us what alternatives it might have, nor what
rowcount estimates it's deriving, so it's hard to comment about that.

> However, if I remove the last part and query for "select * from kvstore
> where deviceid = 7 AND (locid >= 1410929 AND locid <= 1690468)", indices
> are used and everything works nicely.

Um ... I wonder whether you've been careless about whether OR binds
tighter or looser than AND. Are you expecting the deviceid condition
to apply to both locid ranges? Because it doesn't, with the query
written like that. Maybe you just need more parentheses.

regards, tom lane