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