This has obvious semantic disdvantages (what if foo is an expensive
function?);
Agree.
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:
Append
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]
WWW: http://www.sigaev.ru/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
