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_data_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 path
> > ~ '*.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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
--
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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
