Re: [PERFORM] Regex performance issue

2006-12-03 Thread Alexandru Coseru
t; <[EMAIL PROTECTED]> To: "Alexandru Coseru" <[EMAIL PROTECTED]> Cc: "Heikki Linnakangas" <[EMAIL PROTECTED]>; Sent: Sunday, December 03, 2006 6:05 AM Subject: Re: [PERFORM] Regex performance issue "Alexandru Coseru" <[EMAIL PROTECTED]> w

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Oleg Bartunov
D]>; Sent: Saturday, December 02, 2006 10:54 PM Subject: Re: [PERFORM] Regex performance issue I may miss something but I'd use tsearch2. Check intdict dictionary for basic idea - http://www.sai.msu.su/~megera/wiki/Gendict Oleg On Sat, 2 Dec 2006, Alexandru Coseru wrote: Hello..

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Tom Lane
"Alexandru Coseru" <[EMAIL PROTECTED]> writes: > Anyway , anybody has a clue why this regex is that CPU intensive ? The EXPLAIN result you posted offers *no* evidence that the regexp is CPU intensive. All you know is that it took 850+ msec to fetch 5200 rows from disk and apply the regexp filter

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Scott Marlowe
On Sun, 2006-12-03 at 02:53 +0200, Alexandru Coseru wrote: > Hello.. > > Thanks for the tip , i think i have got the ideea.. > > I'm too tired too , and i will try it tommorow. > > > Anyway , anybody has a clue why this regex is that CPU intensive ? I did > not saw the light on my drives blin

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Alexandru Coseru
;Oleg Bartunov" To: "Alexandru Coseru" <[EMAIL PROTECTED]> Cc: "Dave Dutcher" <[EMAIL PROTECTED]>; Sent: Saturday, December 02, 2006 10:54 PM Subject: Re: [PERFORM] Regex performance issue I may miss something but I'd use tsearch2. Check intdict dictionary

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Alexandru Coseru
PROTECTED]> To: "Alexandru Coseru" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 03, 2006 12:35 AM Subject: Re: [PERFORM] Regex performance issue Alexandru Coseru wrote: Hello.. I cannot use the first advice , because i'm not aware of the prefix length in the datab

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Heikki Linnakangas
Alexandru Coseru wrote: Hello.. I cannot use the first advice , because i'm not aware of the prefix length in the database... This is why i'm ordering after length(prefix).. On the 2nd one , i'm not sure that i can follow you.. Ok, let me try again :) asterisk=> select * from destlist LI

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Alexandru Coseru
Linnakangas" <[EMAIL PROTECTED]> To: "Alexandru Coseru" <[EMAIL PROTECTED]> Cc: "Dave Dutcher" <[EMAIL PROTECTED]>; Sent: Sunday, December 03, 2006 12:04 AM Subject: Re: [PERFORM] Regex performance issue Alexandru Coseru wrote: I cannot use LIKE , b

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Heikki Linnakangas
Alexandru Coseru wrote: I cannot use LIKE , because the order of the match is reversed. The prefix column is containing telephone destinations. IE:^001 - US , ^0039 Italy , etc.. Maybe you could create a functional index on substr(prefix>)? It might restrict the result set prior to applyi

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Oleg Bartunov
something.. Regards Alex - Original Message - From: "Dave Dutcher" <[EMAIL PROTECTED]> To: "'Alexandru Coseru'" <[EMAIL PROTECTED]>; Sent: Saturday, December 02, 2006 10:36 PM Subject: RE: [PERFORM] Regex performance issue -Original M

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Alexandru Coseru
ROTECTED]>; Sent: Saturday, December 02, 2006 10:36 PM Subject: RE: [PERFORM] Regex performance issue -Original Message- From: [EMAIL PROTECTED] On Behalf Of Alexandru Coseru asterisk=> explain analyze SELECT * FROM destlist WHERE '0039051248787' ~ pr

Re: [PERFORM] Regex performance issue

2006-12-02 Thread Dave Dutcher
> -Original Message- > From: [EMAIL PROTECTED] On Behalf Of Alexandru Coseru > asterisk=> explain analyze SELECT * FROM destlist WHERE > '0039051248787' ~ > prefix AND id_ent='-2' AND dir=0 ORDER by length(prefix) DESC; > > > Q

[PERFORM] Regex performance issue

2006-12-02 Thread Alexandru Coseru
Hello.. I have a low performance problem with regexp. Here are the details: asterisk=> explain analyze SELECT * FROM destlist WHERE '0039051248787' ~ prefix AND id_ent='-2' AND dir=0 ORDER by length(prefix) DESC; QUERY PLAN -