Re: Index not used with IS NULL

>
>
>I suggest rethinking your schema: whatever you are using NULL to
>represent does not fit very well with SQL's idea of NULL semantics.
>In particular, the notion that "NULL = NULL" should yield true is
>going to get you in all kinds of trouble.
>

Oh, no, it is not really a notion of "NULL=NULL", as I said, I only use
it as a workaround for postgres inability to use index with null keys.

Tom, as you said in your message "we do index nulls" - why do you index
them, if there is no way to use those index values? :-) I mean, if they
were not in the index at all, I could understand that, but they are
already there, and not used, just because of some syntactical difference
between 'is null' and other operators??? That looks very weird to me.

Of course, I would not want to use the 'notion of null=null' if "is
null" worked the same way, but, as you said yourself, it doesn't... So
what do I do?
As for "rethinking my schema"... I would appreciate any suggestions...
There are many instances where I need to have nulls in the indexes, here
is the simplest one:

create table trees
(
id serial primary key,
parent int references trees on delete cascade on update cascade
data text
);
create unique index trees_idx on trees (parent, id);

A row in the table is a tree node. A node can have one parent, ot no
parent at all.
About the only way to do this I know (aside from hacking around and
inserting "dummy" rows into the table) is to use null as parent values
for the nodes with no parents, but then a query like select * from trees
where parent is null will take forever if the table is any large...

What do you recommend? Predicate indexes? Waste of space... What else?

And what exactly is being able to just say something like 'select * from
trees where parent == null' to work around the syntactical problem of is
null not being an operator?

My only real problem with this is it being so complicated to set up. And
I don't really understand what's wrong with it conceptually. To me, it
looks like mereley a wrokaround for a problem with postgres parser (or
planner?) not being able to treat is null as an operator for indexing
purposes.

Dima