Thread: Adjacency List & total item counts

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

use contrib/ltree

     Oleg
On Tue, 9 Aug 2005, Ben wrote:

> 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
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83