Re: Index not used with IS NULL

Dima Tkach <dmitry@openratings.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