Re: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

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(dot)g(dot)johnston(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>