From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Patches <pgsql-patches(at)postgresql(dot)org> |
Subject: | Re: Bundle of patches |
Date: | 2006-12-04 18:46:15 |
Message-ID: | 45746CF7.1070801@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
> 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: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-12-04 19:04:19 | Postgres95 archives in mbox format |
Previous Message | Tom Lane | 2006-12-04 18:45:55 | Re: Bundle of patches |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-12-04 19:04:26 | Re: Bundle of patches |
Previous Message | Tom Lane | 2006-12-04 18:45:55 | Re: Bundle of patches |