Thanks for the quick reply David! However I am still unsure how these two queries are not relationally equivalent. I am struggling to find a counterexample where the first and third query (in email, not in gist) would yield different results. Any ideas?
Jano On Mon, Jul 21, 2014 at 11:31 PM, David G Johnston < david.g.johns...@gmail.com> wrote: > johno wrote > > The question is... why is the query planner unable to make this > > optimization for the slow query? What am I missing? > > Short answer - your first and last queries are not relationally equivalent > and the optimizer cannot change the behavior of the query which it is > optimizing. i.e. you did not make an optimization but rather choose to > reformulate the question so that it could be answered more easily while > still providing an acceptable answer. > > The question main question is better phrased as: > > Give me 100 updated at t(0) but only that are subsequent to a given ID. If > there are less than 100 such records give me enough additional rows having > t > > t(0) so that the total number of rows returned is equal to 100. > > Both queries give the same answer but only due to the final LIMIT 100. They > arrive there in different ways which necessitates generating different > plans. At a basic level it is unable to push down LIMIT into a WHERE > clause > and it cannot add additional sub-queries that do not exist in the original > plan - which includes adding a UNION node. > > David J. > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Slow-query-with-indexed-ORDER-BY-and-LIMIT-when-using-OR-d-conditions-tp5812282p5812285.html > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
