Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-12-04 19:04:19
Subject: Postgres95 archives in mbox format
Previous:From: Tom LaneDate: 2006-12-04 18:45:55
Subject: Re: Bundle of patches

pgsql-patches by date

Next:From: Tom LaneDate: 2006-12-04 19:04:26
Subject: Re: Bundle of patches
Previous:From: Tom LaneDate: 2006-12-04 18:45:55
Subject: Re: Bundle of patches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group