pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruno Wolff III <bruno@wolff.to>
To: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Brown <kevin@sysexperts.com>
Cc: 'pgsql-hackers@postgresql.org' <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] not using index for select min(...)
Date: Sat, 1 Feb 2003 20:41:56 -0600
Message-ID: <20030202024156.GA3353@wolff.to> (raw)
In-Reply-To: <87el6rsqez.fsf@stark.dyndns.tv>
References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com>
	<200301311531.12605.josh@agliodbs.com>
	<20030201040954.GK15936@perrin.int.nxad.com>
	<4278.1044074132@sss.pgh.pa.us>
	<20030201164336.GP12957@filer>
	<7647.1044119012@sss.pgh.pa.us>
	<87el6rsqez.fsf@stark.dyndns.tv>

On Sat, Feb 01, 2003 at 15:21:24 -0500,
  Greg Stark <gsstark@mit.edu> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> That just means you need some way for aggregates to declare which records they
> need. The only values that seem like they would be useful would be "first
> record" "last record" and "all records". Possibly something like "all-nonnull
> records" for things like count(), but that might be harder.

I don't see how this is going to be all that useful for aggregates in general.
min and max are special and it is unlikely that you are going to get much
speed up for general aggregate functions. For the case where you really
only need to scan a part of the data (say skipping nulls when nearly all
of the entries are null), a DBA can add an appropiate partial index and
where clause. This will probably happen infrequently enough that adding
special checks for this aren't going to pay off.

For min and max, it seems to me that putting special code to detect these
functions and replace them with equivalent subselects in the case where
an index exists (since a sort is worse than a linear scan) is a possible
long term solution to make porting easier.

In the short term education is the answer. At least the documentation of the
min and max functions and the FAQ, and the section with performance tips
should recommend the alternative form if there is an appropiate index.



view thread (12+ messages)

Message-ID: <20030202024156.GA3353@wolff.to>
Permalink:  ../20030202024156.GA3353@wolff.to/
Also on:    postgresql.org/message-id/20030202024156.GA3353@wolff.to

 · 

reply

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: bruno@wolff.to, gsstark@mit.edu, tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] not using index for select min(...)
  In-Reply-To: <20030202024156.GA3353@wolff.to>
  References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com> <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <4278.1044074132@sss.pgh.pa.us> <20030201164336.GP12957@filer> <7647.1044119012@sss.pgh.pa.us> <87el6rsqez.fsf@stark.dyndns.tv> <20030202024156.GA3353@wolff.to>

* 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