From: Greg Stark <gsstark@mit.edu>
To: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: sequential scan on select distinct
Date: 07 Oct 2004 13:08:11 -0400
Message-ID: <87pt3u78xw.fsf@stark.xeocode.com> (raw)
In-Reply-To: <opsfhzxvqccq72hf@musicbox>
References: <200410061130.58625.ole@freiheit.com>
<opsff1mmp8cq72hf@musicbox>
<874ql7ztnb.fsf@stark.xeocode.com>
<29194.1097091506@sss.pgh.pa.us>
<opsfhzxvqccq72hf@musicbox>
Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> writes:
> I see this as a minor annoyance only because I can write GROUP BY
> instead of DISTINCT and get the speed boost. It probably annoys people
> trying to port applications to postgres though, forcing them to rewrite
> their queries.
Yeah, really DISTINCT and DISTINCT ON are just special cases of GROUP BY. It
seems it makes more sense to put the effort into GROUP BY and just have
DISTINCT and DISTINCT ON go through the same code path. Effectively rewriting
it internally as a GROUP BY.
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.
--
greg
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: gsstark@mit.edu, lists@boutiquenumerique.com, tgl@sss.pgh.pa.us
Subject: Re: sequential scan on select distinct
In-Reply-To: <87pt3u78xw.fsf@stark.xeocode.com>
* 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