On Mon, Apr 18, 2011 at 5:38 PM, Jesper Krogh <jes...@krogh.cc> wrote: >> order by case when (complex expresssion) 1 when (complex expression) 2 >> else 3 > > How come that expression be relevant? There is only one sortkey and no > limit, so no matter what it should clearly get the full resultset in all > cases.
Sure, imagine there are more order by clauses with this one as the last one. > Yes, as with all other cases it would be hard to get the optimum, but > there is also cases where it is straightforward, say when the secondary > sort column has an ndistinct of -1 (or similar close to). The current > standard > assumption is that 2 columns are unrelated, that would also work here. (As > good as is > does similar places in PG). I'm not following what you mean with the secondary column having ndistinct of -1. Actually it seems to me a reasonable low-hanging fruit to reach for would be when the initial column has an ndistinct of -1 which is actually kind of common. A lot of SQL queries end up being written with GROUP BY primary_key, other_column, other_column, other_column just to get those other columns to be queryable. If we implemented the SQL standard "dependent" columns feature this would be unnecessary but we don't and even if we did people would still build schemas and queries that defeat the optimization. In these cases we probably do have ndistinct -1 for one of the columns and therefore that an index on that column alone would give us almost the right ordering and quite likely exactly the right ordering. If we buffered the outputs for any distinct value and output sorted them if there were multiple rows. It would probably somewhat worse if we guess wrong though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
