Re: any way for ORDER BY x to imply NULLS FIRST in 8.3?

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Not unless it's locked down at initdb time. Otherwise flipping the
>> value bars you from using every existing index ... including those
>> on the system catalogs ... which were made with the other setting.

> Surely if we added this we would also add explicit NULLS LAST clauses to all
> system catalog indexes and system views and make explicitly constructed scans
> in the backend use NULLS LAST.

No, that's not the point; the point is that the performance of
*user-issued* queries (or even more to the point, psql or pg_dump-issued
queries) against the system catalogs would go to pot if they didn't
match the catalog ordering, and a run-time-dependent interpretation of
ORDER BY would make it very likely that the queries don't match, no
matter which underlying index ordering is installed.

Now, most if not all of the system indexes are on NOT NULL columns, so
one possible avenue to resolving that objection would be to teach the
planner that null sort direction can be disregarded when determining
whether an index on a not-null column matches a query. But that already
is making the patch 10x larger and more subtle than what Simon thinks
he's proposing; and I doubt it's the only change we'd find we needed.

A more general objection is that causing query semantics to change in
subtle ways based on a GUC variable has more often than not proven to be
a bad idea.

regards, tom lane