On E, 2005-09-19 at 11:24 +0200, Martijn van Oosterhout wrote: > On Mon, Sep 19, 2005 at 11:13:05AM +0300, Hannu Krosing wrote: > > > (1) IS NULL is not an indexable operation, so no, not without > > > significant overhaul of the index AM API. > > > > But we do store NULLs in indexes, so why is it not indexable? > > > > This is either an interface bug (not making use of stored info) or > > storage bug (wasting space storing unneccessary info) > > Err, indexes used to not store NULLs to save space. However, it turns > out that SQL UNIQUE has something to say about NULLs in unique columns > so they had to be included.
surely not UNIQUE hannu=# create table tabuniq(i int ); CREATE TABLE hannu=# create index tabuniq_ndx on tabuniq(i); CREATE INDEX hannu=# insert into tabuniq values(1); INSERT 20560497 1 hannu=# insert into tabuniq values(2); INSERT 20560498 1 hannu=# insert into tabuniq values(null); INSERT 20560499 1 hannu=# insert into tabuniq values(null); INSERT 20560500 1 maybe the problem is with PRIMARY KEY > However, the machinary to decide if an index is usable assumes that > usable operators have two arguments and IS NULL isn't really an > operator in the PostgreSQL sense and doesn't have two arguments either. > > *If* that can be fixed, then we can be more flexible. But if it were > easy it would have been done long ago... sure :) -- Hannu Krosing <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings