Thread: tree structure photo gallery date quiery
Hi folks. I'm looking at the possibility of implementing a photo gallery for my web site with a tree structure, something like: create table gallery ( id serial, parent int4, name varchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. How would I go about creating a view to show a) the number of photos in a gallery and b) the timestamp of the most recent addition for a gallery, so that it interrogates all sub-galleries? For example NYMR should return 3, 2004-11-10 12:12, Middleton should return 1, 2004-01-01 09:12:12 and Root should return 4, 2004-11-10 12:12:00 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > Hi folks. > > I'm looking at the possibility of implementing a photo gallery for my > web site with a tree structure > How would I go about creating a view to show a) the number of photos in > a gallery and b) the timestamp of the most recent addition for a > gallery, so that it interrogates all sub-galleries? nested-tree helps you associate a numeric interval [l,r] with each record of a tree and let father interval include all its children intervals and brother intervals never intersect see the article http://sf.net/projects/redundantdb for detailed examples and templates
On Tue, 2004-11-16 at 11:29 +0000, Gary Stainburn wrote: > > How would I go about creating a view to show a) the number of photos > in > a gallery and b) the timestamp of the most recent addition for a > gallery, so that it interrogates all sub-galleries? There isn't a very simple answer to that question because you don't have enough information. To make that view, you require there to be a maximum depth to the galleries (say 3 galleries deep only -- including root) OR you need another structure which represents the relationship between all of the galleries. For the latter, something like gallery_lookup(id, cid, nest_depth): 1 1 0 2 2 0 3 3 0 4 4 0 5 5 0 6 6 0 7 7 0 1 2 1 1 3 1 1 4 2 1 5 2 1 6 2 1 7 2 2 4 1 2 5 1 2 6 1 3 7 1 Now that you know the relationship between them all, you can quickly and easily determine all galleries underneath the top level one. Sorry, don't know the technical term, if there is one, for this operation. Now lets make a pair of views: CREATE VIEW gallery_aggregate AS SELECT id, name, sum(CASE WHEN pid IS NULL THEN 0 ELSE 1 END) AS photocount, max(added) AS max_addedFROM galleryLEFTOUTER JOIN photos USING (id)GROUP BY id, name; CREATE VIEW gallery_view_you_want AS SELECT name, sum(photocount), max(max_added)FROM galleryJOIN gallery_lookup AS gl USING (id) JOIN gallery_aggregateAS ga ON (gl.cid = ga.id)GROUP BY name; There are plenty of steps you can take to make this both faster and/or use less storage; optimize aggregates, use a function to calculate the 'gallery_lookup' contents, etc. None of this has been tested. --
> I'm looking at the possibility of implementing a photo gallery for my > web site with a tree structure, something like: You don't really want a tree structure, because one day you'll want to put the same photo in two galleries. Suppose you take a very interesting photo of celery during your trip to china, you might want to create a 'Trip to China' folder, and also a 'Celery' folder for your other celery photos... well, if you don't like vegetables, it also works with people, moods, geographic regions, themes, etc. You could define this structure : You could then define tables describing themes, and/or keywords, link photos with these themes and keywords, and define a folder as either being a specific collection of photos, or as a collection of one or several themes. From a tree, it becomes a bit more like a graph. Themes can also be organized and relationed together. This opens the path to easy searching and cataloguing ; is not that much more difficult to do, and in the end you'll have a much better system. > How would I go about creating a view to show a) the number of photos in > a gallery and b) the timestamp of the most recent addition for a > gallery, so that it interrogates all sub-galleries? If you're concerned about performance, you should do this in a materialized view updated with triggers. If you can afford a seq scan on every time, a few stored procs should do the trick.
On Tuesday 16 November 2004 1:08 pm, sad wrote: > On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > > Hi folks. > > > > I'm looking at the possibility of implementing a photo gallery for > > my web site with a tree structure > > > > How would I go about creating a view to show a) the number of > > photos in a gallery and b) the timestamp of the most recent > > addition for a gallery, so that it interrogates all sub-galleries? > > nested-tree helps you > associate a numeric interval [l,r] with each record of a tree > and let father interval include all its children intervals > and brother intervals never intersect > > see the article http://sf.net/projects/redundantdb > for detailed examples and templates Hi Sad, I had actually started working on this because I found an old list posting archived on the net at http://www.net-one.de/~ks/WOoK/recursive-select. As you can see below, I've got the tree structure working and can select both a node's superiors and it's subordinates. Using these I can also find a node's last added date and photo count. However, I've got two problems. Firstly, below I've got the two example selects for listing owners and owned nodes. I can't work out how to convert these two parameterised selects into views. Secondly, in order to get the results shown here, I've had to write two seperate but similar pl/pgsql functions to return the photo_count and photo_updated columns, which result in 2 * select per call * twice per line * 7 lines = 28 selects Is there a more efficient way? nymr=# select *, photo_count(id), photo_updated(id) from gallery;id | parent | name | photo_count | photo_updated ----+--------+--------------------+-------------+------------------------ 1 | 0 | Root | 4 |2004-11-10 12:12:00+00 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01 6 | 2 | From The Footplate | 0 | 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 (7 rows) Below is everything I have so far, including one of the functions I'm using: create table gallery ( id serial, parent int4, name varchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); create table tree ( -- seperate for now to ease development id int4 references gallery not null, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. copy "tree" from stdin; 1 1 14 2 2 9 3 10 13 4 3 4 5 5 6 6 7 8 7 11 12 \. -- select leaf and parents -- want to convert to a view so I can type something like -- 'select * from root_path where id = 7; nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7;id | parent | name ----+--------+-------------------- 1 | 0 | Root 3 | 1 | Middleton 7 | 3 | From The Footplate (3 rows) -- Select parent and subordinates - also want to convert to view nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1;id | lft | rgt | id | parent | name ----+-----+-----+----+--------+-------------------- 1 | 1 | 14 | 1 | 0 | Root 2 | 2 | 9 | 2 | 1 | NYMR3 | 10 | 13 | 3 | 1 | Middleton 4 | 3 | 4 | 4 | 2 | Steam Gala 5 | 5 | 6 | 5 | 2 | DieselGala 6 | 7 | 8 | 6 | 2 | From The Footplate 7 | 11 | 12 | 7 | 3 | From The Footplate (7 rows) -- use the one above to select photos - another view nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 1 nymr(# );count | max -------+------------------------ 4 | 2004-11-10 12:12:00+00 (1 row) nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 2 nymr(# );count | max -------+------------------------ 3 | 2004-11-10 12:12:00+00 (1 row) nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 3 nymr(# );count | max -------+------------------------ 1 | 2004-01-01 09:12:12+00 (1 row) Here is the photo_count function, photo_updates just has differnt attribute names/types create function photo_count(int4) returns int4 as 'DECLARE gallery_id alias for $1; pcount int4; begin select count(pid) into pcount from photos where id in ( select p1.id from tree as p1, tree as p2 where p1.lft betweenp2.lft and p2.rgt and p2.id = gallery_id ); return pcount; end' language 'plpgsql'; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary, If you're not to worried about tying yourself to Postgres and you're sure you want to create a tree structure, you may want to check out the ltree contrib module. It will allow you to create an index over the entire tree, and will allow you to use real names instead of INTs for the nodes in the tree. ltree will also allow you to have one particular node at different points in the tree. On Wed, 17 Nov 2004 00:35:50 +0000, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote: > On Tuesday 16 November 2004 1:08 pm, sad wrote: > > > > On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > > > Hi folks. > > > > > > I'm looking at the possibility of implementing a photo gallery for > > > my web site with a tree structure > > > > > > How would I go about creating a view to show a) the number of > > > photos in a gallery and b) the timestamp of the most recent > > > addition for a gallery, so that it interrogates all sub-galleries? > > > > nested-tree helps you > > associate a numeric interval [l,r] with each record of a tree > > and let father interval include all its children intervals > > and brother intervals never intersect > > > > see the article http://sf.net/projects/redundantdb > > for detailed examples and templates > > Hi Sad, > > I had actually started working on this because I found an old list > posting archived on the net at > http://www.net-one.de/~ks/WOoK/recursive-select. > > As you can see below, I've got the tree structure working and can select > both a node's superiors and it's subordinates. Using these I can also > find a node's last added date and photo count. > > However, I've got two problems. Firstly, below I've got the two example > selects for listing owners and owned nodes. I can't work out how to > convert these two parameterised selects into views. > > Secondly, in order to get the results shown here, I've had to write > two seperate but similar pl/pgsql functions to return the photo_count > and photo_updated columns, which result in > 2 * select per call * twice per line * 7 lines = 28 selects > > Is there a more efficient way? > > nymr=# select *, photo_count(id), photo_updated(id) from gallery; > id | parent | name | photo_count | photo_updated > ----+--------+--------------------+-------------+------------------------ > 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00 > 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 > 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 > 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 > 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01 > 6 | 2 | From The Footplate | 0 | > 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 > (7 rows) > > Below is everything I have so far, including one of the functions I'm > using: > > > > create table gallery ( > id serial, > parent int4, > name varchar(40), > primary key (id)); > > create table photos ( > pid serial, > id int4 references gallery not null, > added timestamp, > pfile varchar(128) not null, > pdesc varchar(40) not null, > primary key (pid)); > > > create table tree ( -- seperate for now to ease development > id int4 references gallery not null, > lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), > rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), > CONSTRAINT order_okay CHECK (lft < rgt) ); > > > > > copy "gallery" from stdin; > 1 0 Root > 2 1 NYMR > 3 1 Middleton > 4 2 Steam Gala > 5 2 Diesel Gala > 6 2 From The Footplate > 7 3 From The Footplate > \. > > copy "photos" from stdin; > 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine > 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed > 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla > 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey > \. > > copy "tree" from stdin; > 1 1 14 > 2 2 9 > 3 10 13 > 4 3 4 > 5 5 6 > 6 7 8 > 7 11 12 > \. > > -- select leaf and parents > -- want to convert to a view so I can type something like > -- 'select * from root_path where id = 7; > nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 > where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7; > id | parent | name > ----+--------+-------------------- > 1 | 0 | Root > 3 | 1 | Middleton > 7 | 3 | From The Footplate > (3 rows) > > -- Select parent and subordinates - also want to convert to view > nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where > g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; > id | lft | rgt | id | parent | name > ----+-----+-----+----+--------+-------------------- > 1 | 1 | 14 | 1 | 0 | Root > 2 | 2 | 9 | 2 | 1 | NYMR > 3 | 10 | 13 | 3 | 1 | Middleton > 4 | 3 | 4 | 4 | 2 | Steam Gala > 5 | 5 | 6 | 5 | 2 | Diesel Gala > 6 | 7 | 8 | 6 | 2 | From The Footplate > 7 | 11 | 12 | 7 | 3 | From The Footplate > (7 rows) > > -- use the one above to select photos - another view > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 1 > nymr(# ); > count | max > -------+------------------------ > 4 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 2 > nymr(# ); > count | max > -------+------------------------ > 3 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 3 > nymr(# ); > count | max > -------+------------------------ > 1 | 2004-01-01 09:12:12+00 > (1 row) > > Here is the photo_count function, photo_updates just has differnt > attribute names/types > > create function photo_count(int4) returns int4 as 'DECLARE > gallery_id alias for $1; > pcount int4; > begin > select count(pid) into pcount from photos where id in ( > select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft > and p2.rgt and p2.id = gallery_id > ); > return pcount; > end' language 'plpgsql'; > > > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer
Gary, if you need really fast solution for you task and dont't afraid non-standard soltion, take a look on contrib/ltree module. http://www.sai.msu.su/~megera/postgres/gist/ltree/ Oleg On Wed, 17 Nov 2004, Gary Stainburn wrote: > On Tuesday 16 November 2004 1:08 pm, sad wrote: >> On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: >>> Hi folks. >>> >>> I'm looking at the possibility of implementing a photo gallery for >>> my web site with a tree structure >>> >>> How would I go about creating a view to show a) the number of >>> photos in a gallery and b) the timestamp of the most recent >>> addition for a gallery, so that it interrogates all sub-galleries? >> >> nested-tree helps you >> associate a numeric interval [l,r] with each record of a tree >> and let father interval include all its children intervals >> and brother intervals never intersect >> >> see the article http://sf.net/projects/redundantdb >> for detailed examples and templates > > Hi Sad, > > I had actually started working on this because I found an old list > posting archived on the net at > http://www.net-one.de/~ks/WOoK/recursive-select. > > As you can see below, I've got the tree structure working and can select > both a node's superiors and it's subordinates. Using these I can also > find a node's last added date and photo count. > > However, I've got two problems. Firstly, below I've got the two example > selects for listing owners and owned nodes. I can't work out how to > convert these two parameterised selects into views. > > Secondly, in order to get the results shown here, I've had to write > two seperate but similar pl/pgsql functions to return the photo_count > and photo_updated columns, which result in > 2 * select per call * twice per line * 7 lines = 28 selects > > Is there a more efficient way? > > nymr=# select *, photo_count(id), photo_updated(id) from gallery; > id | parent | name | photo_count | photo_updated > ----+--------+--------------------+-------------+------------------------ > 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00 > 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 > 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 > 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 > 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01 > 6 | 2 | From The Footplate | 0 | > 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 > (7 rows) > > Below is everything I have so far, including one of the functions I'm > using: > > create table gallery ( > id serial, > parent int4, > name varchar(40), > primary key (id)); > > create table photos ( > pid serial, > id int4 references gallery not null, > added timestamp, > pfile varchar(128) not null, > pdesc varchar(40) not null, > primary key (pid)); > > > create table tree ( -- seperate for now to ease development > id int4 references gallery not null, > lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), > rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), > CONSTRAINT order_okay CHECK (lft < rgt) ); > > > copy "gallery" from stdin; > 1 0 Root > 2 1 NYMR > 3 1 Middleton > 4 2 Steam Gala > 5 2 Diesel Gala > 6 2 From The Footplate > 7 3 From The Footplate > \. > > copy "photos" from stdin; > 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine > 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed > 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla > 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey > \. > > copy "tree" from stdin; > 1 1 14 > 2 2 9 > 3 10 13 > 4 3 4 > 5 5 6 > 6 7 8 > 7 11 12 > \. > > -- select leaf and parents > -- want to convert to a view so I can type something like > -- 'select * from root_path where id = 7; > nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 > where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7; > id | parent | name > ----+--------+-------------------- > 1 | 0 | Root > 3 | 1 | Middleton > 7 | 3 | From The Footplate > (3 rows) > > -- Select parent and subordinates - also want to convert to view > nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where > g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; > id | lft | rgt | id | parent | name > ----+-----+-----+----+--------+-------------------- > 1 | 1 | 14 | 1 | 0 | Root > 2 | 2 | 9 | 2 | 1 | NYMR > 3 | 10 | 13 | 3 | 1 | Middleton > 4 | 3 | 4 | 4 | 2 | Steam Gala > 5 | 5 | 6 | 5 | 2 | Diesel Gala > 6 | 7 | 8 | 6 | 2 | From The Footplate > 7 | 11 | 12 | 7 | 3 | From The Footplate > (7 rows) > > -- use the one above to select photos - another view > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 1 > nymr(# ); > count | max > -------+------------------------ > 4 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 2 > nymr(# ); > count | max > -------+------------------------ > 3 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 3 > nymr(# ); > count | max > -------+------------------------ > 1 | 2004-01-01 09:12:12+00 > (1 row) > > Here is the photo_count function, photo_updates just has differnt > attribute names/types > > create function photo_count(int4) returns int4 as 'DECLARE > gallery_id alias for $1; > pcount int4; > begin > select count(pid) into pcount from photos where id in ( > select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft > and p2.rgt and p2.id = gallery_id > ); > return pcount; > end' language 'plpgsql'; > > 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
O Oleg Bartunov έγραψε στις Nov 17, 2004 : > Gary, > > if you need really fast solution for you task and dont't afraid > non-standard soltion, take a look on contrib/ltree module. > http://www.sai.msu.su/~megera/postgres/gist/ltree/ > > Oleg Oleg how would you compare an ltree solution against a genealogical approach using intarray?? i.e. store for each node, its path to root, e.g. path='{4,2,7}'::int4[] where 4 is the id of the father, 2 of the grandfather, and 7 of the root, whereas root has path is null. That way we can do really fast queries using ~, have indexes on level (path length),first (immediate ancestor),last (root) using C functions that we can easily write and so forth. I have extensively used this approach with success. Can you comment on the pros and cons of each? (int[] with intarray vs ltree). Thanx > _____________________________________________________________ > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus
On Wed, 17 Nov 2004, Achilleus Mantzios wrote: > O Oleg Bartunov ?????? ???? Nov 17, 2004 : > >> Gary, >> >> if you need really fast solution for you task and dont't afraid >> non-standard soltion, take a look on contrib/ltree module. >> http://www.sai.msu.su/~megera/postgres/gist/ltree/ >> >> Oleg > > Oleg how would you compare an ltree solution against a > genealogical approach using intarray?? > i.e. store for each node, its path to root, > e.g. path='{4,2,7}'::int4[] where 4 is the id of the father, > 2 of the grandfather, and 7 of the root, whereas root has > path is null. > That way we can do really fast queries using ~, have indexes > on level (path length),first (immediate ancestor),last (root) > using C functions that we can easily write and so forth. > I have extensively used this approach with success. > Can you comment on the pros and cons of each? (int[] with intarray vs > ltree). well, internally both approaches are very much the same :) if you don't need ltree features like human readable path But I'd like to have some real numbers. Could you spent some time and create test suite and compare genealogical approach and ltree ? I'll add resuts to docs. > > Thanx >> _____________________________________________________________ >> 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 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: 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