From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan on select distinct
Date: Fri, 08 Oct 2004 10:54:59 +0200
Message-ID: <opsfjm1xrmcq72hf@musicbox> (raw)
In-Reply-To: <87pt3u78xw.fsf@stark.xeocode.com>
References: <200410061130.58625.ole@freiheit.com>
<opsff1mmp8cq72hf@musicbox>
<874ql7ztnb.fsf@stark.xeocode.com>
<29194.1097091506@sss.pgh.pa.us>
<opsfhzxvqccq72hf@musicbox>
<87pt3u78xw.fsf@stark.xeocode.com>
> The really tricky part is that a DISTINCT ON needs to know about a
> first()
> aggregate. And to make optimal use of indexes, a last() aggregate as
> well. And
> ideally the planner/executor needs to know something is magic about
> first()/last() (and potentially min()/max() at some point) and that they
> don't
> need the complete set of tuples to calculate their results.
I'm going to be accused of hand-waving again, but please pardon me, I'm
enthusiastic, and I like to propose new idead, you can kick me if you
don't like them or if I put out too much uninformed bull !
Idea :
The aggregate accumulation function could have a way to say :
"stop ! I've had enough of these values ! Get on with the next item in the
GROUP BY clause !"
I don't know how, or if, the planner could use this (guess: no) or the
index scan use this (guess: no) but it would at least save the function
calls. I'd guess this idea is quite useless.
Aggregates could have an additional attribute saying how much values it
will need ('max_rows' maybe). This would prevent the creation of "magic"
aggregates for max() (which is a kind of special-casing), keep it generic
(so users can create magic aggregates like this).
Aggregates already consist of a bunch of functions (start, accumulate,
return retuls) so this could be just another element in this set.
This information would be known ahead of time and could influence the
query plans too. I'm going to wave my hand and say "not too much planning
cost" because I guess the aggregate details are fetched during planning so
fetching one more attribute would not be that long...
For instance first() would have max_rows=1, and users could code a "first
N accumulator-in-array" which would have max_rows=N...
This does not solve the problem of min() and max() which need max_rows=1
only if the result is sorted... hum... maybe another attribute like
max_rows_sorted = 1 for max() and -1 for min() meaning 'first 1' or 'last
1' (or first N or last N)... according to the "order by" clause it would
be known that the 'first N' of an 'order by ... asc' is the same as the
'last N' from an 'order by ... desc'
???
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com
Subject: Re: sequential scan on select distinct
In-Reply-To: <opsfjm1xrmcq72hf@musicbox>
* 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