pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Clark <matt@ymogen.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: scott.marlowe <scott.marlowe@ihs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow plan for min/max
Date: Tue, 9 Sep 2003 01:42:03 +0100
Message-ID: <LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net> (raw)
In-Reply-To: <9768.1063064551@sss.pgh.pa.us>

> "Matt Clark" <matt@ymogen.net> writes:
> > Actually, referring down to later parts of this thread, why can't this
> > optimisation be performed internally for built-in types?  I
> understand the
> > issue with aggregates over user-defined types, but surely
> optimising max()
> > for int4, text, etc is safe and easy?
>
> I can't see that the datatype involved has anything to do with it.
> None of the issues that come up in making the planner do this are
> datatype-specific.  You could possibly avoid adding some columns
> to pg_aggregate if you instead hard-wired the equivalent knowledge
> (for builtin types only) into some code somewhere, but a patch that
> approached it that way would be rejected as unmaintainable.

I don't pretend to have any useful knowledge of the internals of this, so
much of what I write may seem like noise to you guys.  The naive question is
'I have an index on X, so finding max(X) should be trivial, so why can't the
planner exploit that triviality?'.  AFAICS the short sophisticated answer is
that it just isn't trivial in the general case.

Upon rereading the docs on aggregates I see that it really isn't trivial at
all.  Not even knowing things like 'this index uses the same function as
this aggregate' gets you very far, because of the very general nature of the
implementation of aggs.

So it should be flagged very prominently in the docs that max() and min()
are almost always not what 90% of people want to use 90% of the time,
because indexes do the same job much better for anything other than tiny
tables.

Know what we (OK, I) need?  An explicitly non-aggregate max() and min(),
implemented differently, so they can be optimised.  let's call them
idx_max() and idx_min(), which completely bypass the standard aggregate
code.  Because let's face it, in most cases where you regularly want a max
or a min you have an index defined, and you want the DB to use it.

And I would volunteer to do it, I would, but you really don't want my C in
your project ;-)  I do volunteer to do some doc tweaking though - who do I
talk to?

M




view thread (20+ messages)  latest in thread

Message-ID: <LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net>
Permalink:  ../LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net/
Also on:    postgresql.org/message-id/LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net

 · 

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: matt@ymogen.net, tgl@sss.pgh.pa.us, scott.marlowe@ihs.com
  Subject: Re: slow plan for min/max
  In-Reply-To: <LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net>
  References: <9768.1063064551@sss.pgh.pa.us> <LFEIJBEOKGPDHCEMDGNFGEGJCAAA.matt@ymogen.net>

* 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