Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B40189F9FDA for ; Sat, 2 Dec 2006 20:55:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 19600-05 for ; Sat, 2 Dec 2006 20:55:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.totaltelecom.ro (mail.totaltelecom.ro [89.36.0.12]) by postgresql.org (Postfix) with ESMTP id B396B9F9F66 for ; Sat, 2 Dec 2006 20:55:35 -0400 (AST) Received: from mail.totaltelecom.ro (localhost [127.0.0.1]) by mail.totaltelecom.ro (Postfix) with ESMTP id 53AD334BE24; Sun, 3 Dec 2006 02:55:34 +0200 (EET) Received: by mail.totaltelecom.ro (Postfix, from userid 99) id 4744134BEA2; Sun, 3 Dec 2006 02:55:34 +0200 (EET) Received: from alex (unknown [86.107.100.199]) by mail.totaltelecom.ro (Postfix) with ESMTP id D37C434BE24; Sun, 3 Dec 2006 02:55:32 +0200 (EET) Message-ID: <024d01c71675$b8fa61f0$3cb16956@alex> From: "Alexandru Coseru" To: "Oleg Bartunov" Cc: References: <030b01c71651$984577c0$8300a8c0@tridecap.com> <01ea01c71653$438254e0$3cb16956@alex> Subject: Re: Regex performance issue Date: Sun, 3 Dec 2006 02:55:27 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/21 X-Sequence-Number: 22077 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" To: "Alexandru Coseru" Cc: "Dave Dutcher" ; 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... >> >> 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 | network | tip >> ----+--------+-----+------------+-------------+--------------------+----- >> 1 | -1 | 0 | (^0093) | AFGHANISTAN | AFGHANISTAN | 6 >> 2 | -1 | 0 | (^00937) | AFGHANISTAN | AFGHANISTAN Mobile | 5 >> 3 | -1 | 0 | (^00355) | ALBANIA | ALBANIA | 6 >> 4 | -1 | 0 | (^0035538) | ALBANIA | ALBANIA Mobile | 5 >> 5 | -1 | 0 | (^0035568) | ALBANIA | ALBANIA Mobile | 5 >> 6 | -1 | 0 | (^0035569) | ALBANIA | ALBANIA Mobile | 5 >> 7 | -1 | 0 | (^00213) | ALGERIA | ALGERIA | 6 >> 8 | -1 | 0 | (^0021361) | ALGERIA | ALGERIA Mobile | 5 >> 9 | -1 | 0 | (^0021362) | ALGERIA | ALGERIA Mobile | 5 >> 10 | -1 | 0 | (^0021363) | ALGERIA | ALGERIA Mobile | 5 >> >> >> Now , I have to match a dialednumber (let's say 00213618833) and find >> it's destination...(It's algeria mobile). >> I tried to make with a query of using LIKE , but i was not able to get >> something.. >> >> >> Regards >> Alex >> >> >> >> >> >> ----- Original Message ----- From: "Dave Dutcher" >> To: "'Alexandru Coseru'" ; >> >> Sent: Saturday, December 02, 2006 10:36 PM >> Subject: RE: [PERFORM] Regex performance issue >> >> >>> -----Original Message----- >>> From: pgsql-performance-owner@postgresql.org 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; >>> >>> >>> QUERY PLAN >>> -------------------------------------------------------------- >>> ---------------------------------------------------------------------- >>> Sort (cost=7925.07..7925.15 rows=31 width=67) (actual >>> time=857.715..857.716 rows=2 loops=1) >>> Sort Key: length((prefix)::text) >>> -> Bitmap Heap Scan on destlist (cost=60.16..7924.30 >>> rows=31 width=67) >>> (actual time=2.156..857.686 rows=2 loops=1) >>> Recheck Cond: ((id_ent = -2) AND (dir = 0)) >>> Filter: ('0039051248787'::text ~ (prefix)::text) >>> -> Bitmap Index Scan on destlist_indx2 (cost=0.00..60.16 >>> rows=6193 width=0) (actual time=1.961..1.961 rows=5205 loops=1) >>> Index Cond: ((id_ent = -2) AND (dir = 0)) >>> Total runtime: 857.804 ms >>> (8 rows) >>> >>> "mmumu" btree (prefix varchar_pattern_ops) >>> >> >> I'm surpised Postgres isn't using the index on prefix seeing as the index >> uses the varchar_pattern_ops operator class. It could be that the index >> isn't selective enough, or is Postgres not able to use an index with >> Posix >> regular expressions? The docs seem to say that it can, but I'd be >> curious >> to see what happens if you use LIKE instead of ~. >> >> Dave >> >> >> >> >> >> > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.409 / Virus Database: 268.15.4/563 - Release Date: 12/2/2006 >