Re: weird behaviour on DISTINCT ON

Greg Stark wrote:
> 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)
>

I totally agree and this was clear after having seen what was happening,
my warning was due the fact that in the docs is written nowhere this
drawback. A novice, like I was 4 years ago ( I discover it in the code
only today ), can burn it self.

Regards
Gaetano Mendola