X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 60C1C3A433B for ; Wed, 17 Nov 2004 09:46:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93317-08 for ; Wed, 17 Nov 2004 09:46:06 +0000 (GMT) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id 734B43A3D32 for ; Wed, 17 Nov 2004 09:46:00 +0000 (GMT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id iAH9jkQT013282; Wed, 17 Nov 2004 12:45:46 +0300 (MSK) Date: Wed, 17 Nov 2004 12:45:46 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Achilleus Mantzios Cc: Gary Stainburn , sad , pgsql-sql@postgresql.org Subject: Re: tree structure photo gallery date quiery In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/167 X-Sequence-Number: 19486 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