Re: Index not used with IS NULL

From: Dima Tkach <dmitry(at)openratings(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Index not used with IS NULL
Date: 2003-02-17 04:08:36
Message-ID: 3E506044.8020001@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


>>A row in the table is a tree node. A node can have one parent, ot no
>>parent at all.
>
>
> You're better off making the root node link to itself (compare handling
> of /.. in a Unix filesystem). NULL parent link does not mean "has no
> parent", it means "parent is unknown".
>

Actually, I am afraid, I have to take back my previous message (where I
said, this was a good idea) - after giving it some thought, I don't see
how it will make things better (if anything, it will make them worse).
For example, how would I get the list of the "top-level" (no parent)
nodes given your suggestion?

select * from trees where parent=id

is hardly a good idea, because it just has to be a seq. scan, right?

Right now, I am, at least, able to do

select * from trees where parent == null;

(where '==' is my custom non-strict equivalence operator), that uses an
index scan and works just fine.

Of course, it would be nicer to be able to get away with the standard
sql set of operators, but, I guess, I have to do what I have to do :-(

Dima

P.S. Frankly, I still don't understand what is the big problem with
making 'is null' indexable - as far as I can see, this is purely
syntactical problem, because the btree code itself seems to be able to
handle nulls just fine - it is at the level of the planner the index
option just gets cut off, because it doesn't know what to do with 'is
null'...
I may be missing something of course, but so far, this looks to me like
a very useful feature, that would be very easy to implement too...

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dima Tkach 2003-02-17 04:16:23 Re: Index not used with IS NULL
Previous Message Seth Clarke 2003-02-17 01:46:25 Inquiry From Form [pgsql]