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

johno <jan(dot)suchal(at)gmail(dot)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