Skip site navigation (1) Skip section navigation (2)

Re: weird behaviour on DISTINCT ON

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: weird behaviour on DISTINCT ON
Date: 2005-01-31 18:31:45
Message-ID: 87lla9qwku.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:

> now what do I see is that for each different x value
> the foo is executed more than once, I guess this is because
> the distinct filter out the rows after executing the query.
> 
> Is this behaviour the normal one? Shall be not documented ?

Usually DISTINCT ON is only really useful if you're sorting on something.
Otherwise the choice of which record is output is completely arbitrary.

So the above would typically be something like:

SELECT DISTINCT ON (x), y, foo(x)
 ...
 ORDER BY x, y

Now you can see why every record does need to be looked at to handle that.
In fact the ORDER BY kicks in before output columns are generated so you can
do things like:

SELECT DISTINCT ON (x), y, foo(x)
 ...
 ORDER BY x, y, foo(x)

And of course obviously foo() has to be executed for every record to do this.

Postgres doesn't try to detect cases where it's safe to change the regular
order in which things are done and delay execution of functions whose results
aren't needed right away. 

You could just use

SELECT x, foo(x) from (SELECT x ... GROUP BY x)

-- 
greg


In response to

Responses

pgsql-hackers by date

Next:From: Gaetano MendolaDate: 2005-01-31 18:52:21
Subject: Re: weird behaviour on DISTINCT ON
Previous:From: Marc G. FournierDate: 2005-01-31 18:29:36
Subject: 7.2.7 -> 8.0.1 Bundles Ready ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group