From: Josh Berkus <josh@agliodbs.com>
To: Greg Stark <gsstark@mit.edu>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: slow plan for min/max
Date: Tue, 9 Sep 2003 10:14:03 -0700
Message-ID: <200309091014.03405.josh@agliodbs.com> (raw)
In-Reply-To: <87ad9d52dv.fsf@stark.dyndns.tv>
References: <Pine.LNX.4.33.0309081434150.11709-100000@css120.ihs.com>
<87ad9d52dv.fsf@stark.dyndns.tv>
Greg,
> The only connection to MVCC is that the "obvious" solution doesn't work,
> namely storing a cache of the aggregate in the table information.
Well, that solution also doesn't work if you use a WHERE condition or JOIN,
now does it?
> So what would it take to implement this for "all" aggregates? Where I think
> "all" really just means min(), max(), first(), last().
Um, what the heck are first() and last()? These are not supported aggregates
... table rows are *not* ordered.
> For min() and max() it would have to indicate not only that only the first
> or last record is necessary but also the sort order to impose.
I think Tom already suggested this based on adding a field to CREATE
AGGREGATE. But I think implementation isn't as simple as you think it is.
> Now the problem I see is if there's no index on the sort order imposed, and
> the previous step wasn't a merge join or something else that would return
> the records in order then it's not necessarily any faster to sort the
> records and return only some. It might be for small numbers of records, but
> it might be faster to just read them all in and check each one for min/max
> the linear way.
Yes, Tom mentioned this also. Working out the rules whereby the planner could
decide the viability of index use is a non-trivial task.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, gsstark@mit.edu
Subject: Re: slow plan for min/max
In-Reply-To: <200309091014.03405.josh@agliodbs.com>
References: <Pine.LNX.4.33.0309081434150.11709-100000@css120.ihs.com> <87ad9d52dv.fsf@stark.dyndns.tv> <200309091014.03405.josh@agliodbs.com>
* 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