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: oleg@sai.msu.su, 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.