Re: Index not used with IS NULL

This is a resend, don't know if the first time it got to the list... sorry
if it did.

Hello, sorry for barging in...
I use a similar structure for keeping some some text pages categorized.

CREATE TABLE pages (
id SERIAL NOT NULL PRIMARY KEY,
categ INTEGER,
CONSTRAINT categ_fk FOREIGN KEY(categ) REFERENCES categs(id) ON DELETE CASCADE
);

All the pages that are not contained in a category are marked by categ IS
NULL ( this is like the files in / in a filesystem). If I use other values
than NULL for marking this kind of pages, then the constraint would
complain, but then I can't use an index to find these pages.

Do you have a better solution for this ?

Thanks.

On Mon, 17 Feb 2003, Tom Lane wrote:

> Dima Tkach <dmitry(at)openratings(dot)com> writes:
> > For example, how would I get the list of the "top-level" (no parent)
> > nodes given your suggestion?
> > select * from trees where parent=id
>
> Exactly.
>
> > is hardly a good idea, because it just has to be a seq. scan, right?
>
> Make a partial index if you need it to be fast.
>
> regression=# create table trees (id int, parent int);
> CREATE TABLE
> regression=# explain select * from trees where parent=id;
> QUERY PLAN
> ------------------------------------------------------
> Seq Scan on trees (cost=0.00..22.50 rows=5 width=8)
> Filter: (parent = id)
> (2 rows)
>
> regression=# create index foo on trees(id) where parent=id;
> CREATE INDEX
> regression=# explain select * from trees where parent=id;
> QUERY PLAN
> ------------------------------------------------------------------
> Index Scan using foo on trees (cost=0.00..17.07 rows=5 width=8)
> Filter: (parent = id)
> (2 rows)
>
>
> > 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...
>
> Criticism in the form of patches is more useful than unsubstantiated
> opinions that something is easy.
>
> regards, tom lane