Re: Bundle of patches

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgresql(dot)org, Teodor Sigaev <teodor(at)sigaev(dot)ru>
Cc: Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Bundle of patches
Date: 2006-12-04 18:19:03
Message-ID: 19324.1165256343@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers pgsql-patches

Teodor Sigaev <teodor(at)sigaev(dot)ru> writes:
> 2) ORDER BY .. [ NULLS ( FIRST | LAST ) ]
> http://www.sigaev.ru/misc/NULLS_82-0.5.gz

i haven't looked at the other patches yet, but this one is just horrid :-(
Instead of creating a proper parse-tree representation of the NULLS
FIRST/LAST option, you've kluged it to convert the syntax into a
double-column ordering using "foo IS [NOT] NULL" as the first column.
This has obvious semantic disdvantages (what if foo is an expensive
function?); 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.

I think a reasonable implementation requires introducing an explicit
concept of nulls-first-or-last into the planner's model of sort order,
and probably into btree index opclasses as well. There is already some
discussion about this in the archives with respect to the problem of
handling descending-sort opclasses nicely. (If you just switch the
operators to make a descending-sort opclass, then nulls end up at the
"other end" from where they would otherwise, meaning that a backwards
index scan does something unexpected. We have to fix that or else
descending-sort opclasses can break mergejoins...)

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-12-04 18:35:21 Re: Bundle of patches
Previous Message Jeff Davis 2006-12-04 17:33:02 Re: [HACKERS] getting index columns from information_schema?

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-12-04 18:35:21 Re: Bundle of patches
Previous Message Jie Zhang 2006-12-04 18:13:46 Re: On-disk bitmap index implementation