From: Greg Stark <gsstark@mit.edu>
To: Dror Matalon <dror@zapatec.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Speeding up Aggregates
Date: 09 Oct 2003 19:07:00 -0400
Message-ID: <87ekxm9fjf.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <20031008181819.GJ2979@rlx11.zapatec.com>
References: <20031003202120.GO87525@rlx11.zapatec.com>
<1065217488.91586.3.camel@jester>
<20031003215347.GQ87525@rlx11.zapatec.com>
<1065219028.91586.8.camel@jester>
<87llrvbx0f.fsf@stark.dyndns.tv>
<20031008181819.GJ2979@rlx11.zapatec.com>
Dror Matalon <dror@zapatec.com> writes:
> Actually what finally sovled the problem is repeating the
> dtstamp > last_viewed
> in the sub select
That will at least convince the optimizer to use an index range lookup. But it
still will have to scan every record that matches channel==$1, link==$2, and
dtstamp>$3.
The trick of using limit 1 will be faster still as it only has to retrieve a
single record using the index. But you have to be sure to convince it to use
the index and the way to do that is to list exactly the same columns in the
ORDER BY as are in the index definition.
Even if some of the leading columns are redundant because they'll be constant
for all of the records retrieved. The optimizer doesn't know to ignore those.
> > (This is the thing i pointed out previously in
> > <87el6ckrlu.fsf@stark.dyndns.tv> on Feb 13th 2003 on pgsql-general)
--
greg
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: gsstark@mit.edu, dror@zapatec.com
Subject: Re: Speeding up Aggregates
In-Reply-To: <87ekxm9fjf.fsf@stark.dyndns.tv>
* 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