pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLtree usage..
3+ messages / 2 participants
[nested] [flat]
* Ltree usage..
@ 2002-08-02 16:33 Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-02 17:00 ` Re: Ltree usage.. Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-08-02 16:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: oleg@sai.msu.su
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.*' ;
also is qry3 better to Qry2 in terms of performance?
regds
mallah.
--
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Ltree usage..
2002-08-02 16:33 Ltree usage.. Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-08-02 17:00 ` Oleg Bartunov <oleg@sai.msu.su>
2002-08-03 05:26 ` Re: Ltree usage.. Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 3+ messages in thread
From: Oleg Bartunov @ 2002-08-02 17:00 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Ltree usage..
2002-08-02 16:33 Ltree usage.. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-02 17:00 ` Re: Ltree usage.. Oleg Bartunov <oleg@sai.msu.su>
@ 2002-08-03 05:26 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-08-03 05:26 UTC (permalink / raw)
To: oleg@sai.msu.su; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-03 05:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-02 16:33 Ltree usage.. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-08-02 17:00 ` Oleg Bartunov <oleg@sai.msu.su>
2002-08-03 05:26 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox