pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: 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: 01 Feb 2003 15:21:24 -0500
Message-ID: <87el6rsqez.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <7647.1044119012@sss.pgh.pa.us>
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>

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Kevin Brown <kevin@sysexperts.com> writes:
> > Hmm...any chance, then, of giving aggregate functions a means of
> > asking which table(s) and column(s) the original query referred to so
> > that it could do proper optimization on its own?
> 
> You can't usefully do that without altering the aggregate paradigm.
> It won't help for min() to intuit the answer quickly if the query
> plan is going to insist on feeding every row to it anyway.

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.

> Don't forget that it would also need to be aware of whether there were
> any WHERE clauses, joins, GROUP BY, perhaps other things I'm not
> thinking of.
> 
> In the end, the only reasonable way to handle this kind of thing is
> to teach the query planner about it.  Considering the small number
> of cases that are usefully optimizable (basically only MIN and MAX
> on a single table without any WHERE or GROUP clauses), and the ready
> availability of a SQL-level workaround, it strikes me as a very
> low-priority TODO item.

All true, but I wouldn't be so quick to dismiss it as low-priority. In my
experience I've seen the idiom "select min(foo) from bar" more times than I
can count. The frequency with which this question occurs here probably is
indicative of how much people expect it to work. And it's probably used by a
lot of multi-database applications and in a lot of auto-matically generated
code where it would be hard to hack in special purpose workarounds.

-- 
greg




view thread (12+ messages)  latest in thread

Message-ID: <87el6rsqez.fsf@stark.dyndns.tv>
Permalink:  ../87el6rsqez.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87el6rsqez.fsf@stark.dyndns.tv

 · 

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: 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: <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>

* 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