Re: [HACKERS] weird behaviour on DISTINCT ON
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |my warning was due the fact that in the docs is written nowhere this |drawback. | | | The SELECT reference page already says that the output rows are computed | before applying ORDER BY or DISTINCT. | | regards, tom lane True. I had to say my self: RTFM. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB/8fP7UpzwH2SGd4RAhpFAJ9x3jhMzJ3f94wnlN1DbxRNRQvOzACfXtVp +Zg1pVO7SsETwUx6fxCl7qw= =Q5EW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] weird behaviour on DISTINCT ON
Hi all, I have a query that is something like this: SELECT DISTINCT ON ( x ) x, foo(x) FROM ... 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 ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] weird behaviour on DISTINCT ON
Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] weird behaviour on DISTINCT ON
Greg Stark wrote: Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] weird behaviour on DISTINCT ON
Gaetano Mendola [EMAIL PROTECTED] writes: my warning was due the fact that in the docs is written nowhere this drawback. The SELECT reference page already says that the output rows are computed before applying ORDER BY or DISTINCT. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
