From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions
Date: Mon, 21 Jul 2014 15:14:44 -0700 (PDT)
Message-ID: <1405980884215-5812291.post@n5.nabble.com> (raw)
In-Reply-To: <CACuOPqC+AXr4zGTD+Re8Y7R9RwA1s-h2Vn361-+FNasNMHqgPA@mail.gmail.com>
References: <CACuOPqC9h6vK5rniezSOm2fu44PNp0NrB8kdtapT=xqNzQgidA@mail.gmail.com>
<1405978279538-5812285.post@n5.nabble.com>
<CACuOPqBc7kKiv4JjqQwEJO1PG7iN5XByt9-b9Di2i69k+SV5dA@mail.gmail.com>
<1405979651518-5812289.post@n5.nabble.com>
<CACuOPqC+AXr4zGTD+Re8Y7R9RwA1s-h2Vn361-+FNasNMHqgPA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
johno wrote
> 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.g.johnston@
>> 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.
Try following my lead and bottom-post, please.
Anyway, the query has no clue that because of the final LIMIT 100 that the
two different feeding queries are just going to happen to end up providing
the same result. Maybe, in this particular instance, it is theoretically
possible to make such a proof but generally that is not the case and so such
an optimization has not made into the codebase even if it theoretically
could be done (I'm not convinced it could but do not know enough to explain
to someone else why I have that impression).
I do not know enough to answer why this situation is any different from a
similar partitioning scenario. An example showing exactly what a similar
partitioning query looks like would help in this regard.
If you are looking for considerably more insight into the planner workings
and why it does or doesn't do something you will need to wait for others. I
can, to a reasonable degree, deconstruct a pair of queries and either
explain or guess as to why things are happening but that is mostly applied
deductive reasoning and not because I have any particular insight into the
codebase.
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-c...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions
In-Reply-To: <1405980884215-5812291.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox