Tom Lane <[EMAIL PROTECTED]> writes: > On the other side of the coin, there's the analogy to GROUP BY that Greg > points out --- there's some duplicated functionality there, but again it > doesn't carry over to DISTINCT ON, AFAICS.
The equivalent query for: SELECT DISTINCT ON (x,y) a,b,c FROM ... ORDER BY x,y,z is: SELECT x,y,z,first(a),first(b),first(c) FROM ( SELECT x,y,z,a,b,c FROM ... ORDER BY x,y,z ) GROUP BY x,y Getting the optimizer to treat this as well as DISTINCT ON would be quite a trick. It would probably require the same machinery as getting min() and max() to take maximum advantage of indexes in the face of a GROUP BY clause. That is some sort of field for aggregate functions indicating what subset of the records is sufficient for them and what path they would need for that to be the case. In any case I don't see how you get DISTINCT ON to work without sorting. For min() and max() they could indicate they only need the first field if the input is sorted and the optimizer could decide it's cheaper to pass them every record that do the sort. For first() and last() they would tell the optimizer they only need the first or last record with no particular path but that only works because the rewritten query has an explicit ORDER BY clause. That's about as far as I've thought about it. At the time I thought it would likely be too hard for a first project. I suspect it's too hard for a second project for that matter. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org