Re: Adjacency List & total item counts

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83