From: Tom Lane <tgl@sss.pgh.pa.us>
To: johno <jan.suchal@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions
Date: Mon, 21 Jul 2014 22:53:01 -0400
Message-ID: <4381.1405997581@sss.pgh.pa.us> (raw)
In-Reply-To: <CACuOPqC9h6vK5rniezSOm2fu44PNp0NrB8kdtapT=xqNzQgidA@mail.gmail.com>
References: <CACuOPqC9h6vK5rniezSOm2fu44PNp0NrB8kdtapT=xqNzQgidA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
johno <jan.suchal@gmail.com> writes:
> I am trying to optimize a simple query that returns first 100 rows that
> have been updated since a given timestamp (ordered by timestamp and id
> desc). If there are several rows with the same timestamp I need to a
> second condition, that states that I want to return rows having the given
> timestamp and id > given id.
> The obvious query is
> SELECT * FROM register_uz_accounting_entities
> WHERE effective_on > '2014-07-11' OR (effective_on = '2014-07-11' AND
> id > 1459)
> ORDER BY effective_on, id
> LIMIT 100
A more readily optimizable query is
SELECT * FROM register_uz_accounting_entities
WHERE (effective_on, id) > ('2014-07-11'::date, 1459)
ORDER BY effective_on, id
LIMIT 100
This formulation allows the planner to match both the WHERE and ORDER BY
clauses directly to the two-column index.
> I've tried to optimize this query by pushing down the limit and order by's
> into explicit subselects.
As noted earlier, that's unlikely to be an improvement, because on its
face it specifies more computation. Postgres is not terribly bright
about UNIONs, either.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, jan.suchal@gmail.com
Subject: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions
In-Reply-To: <4381.1405997581@sss.pgh.pa.us>
* 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