Search Postgresql Archives

Adjacency List & total item counts

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

This question is not specific to PostgreSQL but I would like to know
what is the best way to count the number of items in each node from
the leaf to the root? Something like this:

                             Computers (100)
                                    /\
                                   /  \
                     CPU (15)      Memory (85)

I have the following SQL schema:

Tree (
     treeId int,
     parentId int,
     name varchar(250),
)

Item (
    itemId int,
    treeId int,
    expiryDate date
)

Note that the count for the total number of items in each node depends
on the item expiry date, i.e. ignore the item if the expiry date is
older than now().

I have come up with the following solutions but not happy with any one of them:

1) Do a batch count, i.e. count the number of items every 30 minutes.
Using this method defeats the purpose of having the count next to each
node since the number might not be the same as the actual count.

2) Use trigger but this can be slow since it has to recurse the tree
and do the sum every time new item is added.

Thanks
Ben

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux