From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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, 01 Feb 2003 12:03:32 -0500
Message-ID: <7647.1044119012@sss.pgh.pa.us> (raw)
In-Reply-To: <20030201164336.GP12957@filer>
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>
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.
> For instance, for a
> "SELECT min(x) FROM mytable" query, the min() function would be told
> upon asking that it's operating on column x of mytable, whereas it
> would be told "undefined" for the column if the query were "SELECT
> min(x+y) FROM mytable". In the former case, it would be able to do a
> "SELECT x FROM mytable ORDER BY x LIMIT 1" on its own,
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.
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 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: tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] not using index for select min(...)
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>
* 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