Re: Ltree usage..

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.