Re: Bundle of patches

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
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/

In response to

Responses

Browse pgsql-hackers by date

  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

Browse pgsql-patches by date

  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