From: | johno <jan(dot)suchal(at)gmail(dot)com> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions |
Date: | 2014-07-21 22:02:54 |
Message-ID: | CACuOPqC+AXr4zGTD+Re8Y7R9RwA1s-h2Vn361-+FNasNMHqgPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Oh, yes I do understand that if I remove the outer limit, the semantics of
the query would change. However I am looking for the counterexample *with*
the limit clauses. Maybe I just don't understand what relationally
equivalent means, sorry about that.
BTW this is to my understanding a very similar scenario to how partitioned
tables work and push down limit and where conditions. Why is this not
possible in this case?
Jano
On Mon, Jul 21, 2014 at 11:54 PM, David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> johno wrote
> > 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?
>
> Remove the outer LIMIT 100 from both queries...
>
> The first query would return a maximal number of rows that meet the OR
> criteria while the second query would return at most 200 rows since both
> sub-queries would still have their own independent LIMIT 100 clauses.
>
> 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-tp5812282p5812289.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
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-07-21 22:14:44 | Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions |
Previous Message | David G Johnston | 2014-07-21 21:54:11 | Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions |