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 >