Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A47BA474E4E for ; Sat, 3 Aug 2002 01:27:47 -0400 (EDT) Received: from mail-relay.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id C2F3447514C for ; Sat, 3 Aug 2002 01:27:43 -0400 (EDT) Received: (qmail 29451 invoked from network); 3 Aug 2002 05:17:05 -0000 Received: from system67.trade-india-local.com (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 3 Aug 2002 05:17:05 -0000 Content-Type: text/plain; charset="iso-8859-1" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: oleg@sai.msu.su Subject: Re: Ltree usage.. Date: Sat, 3 Aug 2002 10:56:43 +0530 User-Agent: KMail/1.4.1 Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200208031046.32325.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/54 X-Sequence-Number: 8900 Hi Oleg, It does not yeild the correct result for me. I am providing more details this time. path is ltree [] for me not ltree, Column | Type | Modifiers ------------+------------------------+-------------------------------------= ---------------------------- profile_id | integer | not null default nextval('"unified_d= ata_profile_id_seq"'::text) co_name | character varying(255) | city | character varying(100) | path | ltree[] | Indexes: unified_data_path Unique keys: unified_data_co_name_key, unified_data_profile_id_key eg if my sample data set is. profile_id | path ------------+---------------------------------------------------------- 25477 | {0.180.830,0.180.848} 26130 | {0.180.848} 2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849} 26129 | {0.180.848} 26126 | {0.180.848} 26127 | {0.180.848} 26128 | {0.180.848} 24963 | {0.180.830,0.180.848} 26125 | {0.180.848} 7239 | {0.246.256,0.246.282,0.180.848,0.246.857,0.76.1255.1161} (10 rows) what query shud i use to extract profiles where path contains *.64.* and *.= 180.* eg this query SELECT profile_id,path from unified_data where path ~ '*.180.*' and = path ~ '*.64.*' limit 10; profile_id | path ------------+------------------------------------------------- 2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849} 3238 | {0.64.68,0.180.830,0.395.904} 6255 | {0.180.227,0.64.814} 6153 | {0.180.227,0.505.518,0.64.814} 6268 | {0.180.227,0.64.814} 6267 | {0.180.227,0.64.814} 6120 | {0.180.227,0.64.814} 6121 | {0.180.227,0.64.814} 6084 | {0.180.227,0.64.814} 6066 | {0.180.227,0.64.810} (10 rows) gives me the correct result but i am not sure if its the most efficient. I will be using it for medium sized dataset approx 100,000 that there will= be such search on upto four such indexed columns. regds mallah. On Friday 02 August 2002 22:30, Oleg Bartunov wrote: > On Fri, 2 Aug 2002, Rajesh Kumar Mallah. wrote: > > Hi Oleg, > > > > I am trying to use contrib/ltree for one of my applications. > > > > the query below works fine for me. > > > > Qry1: SELECT path from unified_data where path ~ '*.180.*' and pa= th > > ~ '*.1.*'; > > > > is there any way of compacting it for example > > > > Qry2: SELECT path from unified_data where path ~ '*.180.*' or path > > ~ '*.1.*'; is better written as > > Qry3: SELECT path from unified_data where path ~ '*.180|1.*' ; > > Qry2 and Qry3 are equvalent and Qry3 is faster but not much. > But Qry1 is not the same as Qry2 !!! > > Qry1 could be rewritten as: > > SELECT path from unified_data where path @ '180 & 1'; > > > also is qry3 better to Qry2 in terms of performance? > > > > regds > > mallah. > > 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 --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.