This has obvious semantic disdvantages (what if foo is an expensive

but the real problem is that there's no way for the planner
to reason about ordering in this representation.  This patch would
guarantee that an ORDER BY with the NULLS option couldn't use an
indexscan, even if the index sorts nulls at the correct end.

create table foo ( i int);
insert into foo values (1), (5), (NULL);
create index fooidx on foo (i);
set enable_seqscan=off;
set enable_bitmapscan=off;
explain select i from foo order by i asc nulls last;
                            QUERY PLAN
 Index Scan using fooidx on foo  (cost=0.00..12.05 rows=3 width=4)
explain select i from foo order by i desc nulls first;
                                 QUERY PLAN
 Index Scan Backward using fooidx on foo  (cost=0.00..12.05 rows=3 width=4)

Patch is smart enough about "native" NULL's ordering, so it adds quals only if it needed.

Index support of non-"native" NULL's ordering, IMHO, has some correlation with suggested OR-patch. Sorting by ASC NULLS FIRST may done by two index scan with append node:
        Index Scan
                Cond: foo IS NULL
        Index Scan
                Cond: foo IS NOT NULL

I think a reasonable implementation requires introducing an explicit
concept of nulls-first-or-last into the planner's model of sort order,
Agree, but I tried to keep patches independent as possible...

If we will have agreement about ways to resolve, I'll will time to work
further in foreseeable future.
Teodor Sigaev                                   E-mail: [EMAIL PROTECTED]

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to