From: Mark Lewis <mark.lewis@mir3.com>
To: gdavis@refractions.net
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index
Date: Tue, 03 Oct 2006 14:06:52 -0700
Message-ID: <1159909612.18640.80.camel@archimedes> (raw)
In-Reply-To: <4522CD8C.1000108@refractions.net>
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
<20061003000147.GA31475@wolff.to>
<4522AA01.4020709@refractions.net>
<60irj1gr3r.fsf@dba2.int.libertyrms.com>
<4522B5B9.2050206@refractions.net>
<60d599gqcz.fsf@dba2.int.libertyrms.com>
<4522C8D8.4010601@refractions.net>
<10941.1159908489@sss.pgh.pa.us>
<4522CD8C.1000108@refractions.net>
Have you looked into a materialized view sort of approach? You could
create a table which had assetid as a primary key, and max_ts as a
column. Then use triggers to keep that table up to date as rows are
added/updated/removed from the main table.
This approach would only make sense if there were far fewer distinct
assetid values than rows in the main table, and would get slow if you
commonly delete rows from the main table or decrease the value for ts in
the row with the highest ts for a given assetid.
-- Mark Lewis
On Tue, 2006-10-03 at 13:52 -0700, Graham Davis wrote:
> Thanks Tom, that explains it and makes sense. I guess I will have to
> accept this query taking 40 seconds, unless I can figure out another way
> to write it so it can use indexes. If there are any more syntax
> suggestions, please pass them on. Thanks for the help everyone.
>
> Graham.
>
>
> Tom Lane wrote:
>
> >Graham Davis <gdavis@refractions.net> writes:
> >
> >
> >>How come an aggreate like that has to use a sequential scan? I know
> >>that PostgreSQL use to have to do a sequential scan for all aggregates,
> >>but there was support added to version 8 so that aggregates would take
> >>advantage of indexes.
> >>
> >>
> >
> >Not in a GROUP BY context, only for the simple case. Per the comment in
> >planagg.c:
> >
> > * We don't handle GROUP BY, because our current implementations of
> > * grouping require looking at all the rows anyway, and so there's not
> > * much point in optimizing MIN/MAX.
> >
> >The problem is that using an index to obtain the maximum value of ts for
> >a given value of assetid is not the same thing as finding out what all
> >the distinct values of assetid are.
> >
> >This could possibly be improved but it would take a considerable amount
> >more work. It's definitely not in the category of "bug fix".
> >
> > regards, tom lane
> >
> >
>
>
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: mark.lewis@mir3.com, gdavis@refractions.net, cbbrowne@acm.org
Subject: Re: BUG #2658: Query not using index
In-Reply-To: <1159909612.18640.80.camel@archimedes>
* 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