Re: Speeding up Aggregates

Rod Taylor <rbt@rbt.ca> writes:

> On Fri, 2003-10-03 at 17:53, Dror Matalon wrote:
> > On Fri, Oct 03, 2003 at 05:44:49PM -0400, Rod Taylor wrote:
> > >
> > > It is too bad the (channel, link) index doesn't have dtstamp at the end
> > > of it, otherwise the below query would be a gain (might be a small one
> > > anyway).
> > >
> > >   select dtstamp
> > >     from items
> > >    where channel = $1
> > >      and link = $2
> > > ORDER BY dtstamp DESC
> > >    LIMIT 1;
>
> It didn't make a difference even with the 3 term index? I guess you
> don't have very many common values for channel / link combination.

You need to do:

 ORDER BY channel DESC, link DESC, dtstamp DESC

This is an optimizer nit. It doesn't notice that since it selected on channel
and link already the remaining tuples in the index will be ordered simply by
dtstamp.

(This is the thing i pointed out previously in
<87el6ckrlu.fsf@stark.dyndns.tv> on Feb 13th 2003 on pgsql-general)


--
greg