On Tue, Jul 22, 2014 at 4:53 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> johno <jan.suc...@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
>
Yes, but that query has completely different semantics - I can't change
that.
>
> This formulation allows the planner to match both the WHERE and ORDER BY
> clauses directly to the two-column index.
>
Are both fields really used? I was under the impression that only the first
column from index can be used when there is a range query.
>
> > 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.
>
Despite the cost calculation in explain the actual query times are very
different. I get consistent sub 50ms responses from the optimized one
(union with pushing down the limits) and 500+ms for the plain one (when not
using bitmap index scan).
Is this possible optimization considered by query planner or do I have
"force" it?
Thanks again for your time and effort, I appreciate it.
>
> regards, tom lane
>
