Re: [PERFORM] Regex performance issue

On Sun, 3 Dec 2006, Alexandru Coseru wrote: Hello.. I have never used tsearch2 , but at a first glance , i would not see any major improvement , because the main advantage of tsearch is the splitting in words of a phrase.. But here , i only have one word (no spaces). Oh, yes, I was confu

Re: [PERFORM] Regex performance issue

"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

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

Hello.. I have never used tsearch2 , but at a first glance , i would not see any major improvement , because the main advantage of tsearch is the splitting in words of a phrase.. But here , i only have one word (no spaces). Regards Alex - Original Message - From: "Oleg Bartunov

Re: [PERFORM] Regex performance issue

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 blinking , and also vmstat doesn't yeld any blocks in or out... And how can it

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 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

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.. Regards Alex - Original Message - From: "Heikki Linnakangas" <[EMAIL PROTECT

Re: [PERFORM] Regex performance issue

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

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... I cannot use LIKE , because the order of the match is reversed. The prefix column is containing telephone d

Re: [PERFORM] Regex performance issue

Hello... 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.. Here is a small sample: asterisk=> select * from destlist LIMIT 10; id | id_ent | dir | prefix | country | netwo

Re: [PERFORM] Regex performance issue

> -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

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 -

Re: [PERFORM] Dump performance problems following server crash

We dropped into single user mode and ran reindex system - it was my understanding this would reindex them all, including shared catalogs - but perhaps not? Kim Tom Lane wrote: Kim <[EMAIL PROTECTED]> writes: The current dump query running: SELECT t.tableoid, t.oid, t.relname as indexname

Re: [PERFORM] Dump performance problems following server crash

Kim <[EMAIL PROTECTED]> writes: > The current dump query running: > SELECT t.tableoid, t.oid, t.relname as indexname, > pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, t.relnatts as > indnkeys, i.indkey, i.indisclustered, c.contype, c.conname, c.tableoid > as contableoid, c.oid as conoid,

[PERFORM] Dump performance problems following server crash

Hello Performance, Yesterday, with help from the admin list, we took care of a problem we were having with creating tables/views/indexes following a server overheat & crash (an index on pg_attribute was corrupted, causing the create to hang and go through the roof on memory usage until it fail