Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1DEAB9F9F0E for ; Sat, 2 Dec 2006 15:24:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 58887-05 for ; Sat, 2 Dec 2006 15:24:11 -0400 (AST) X-Greylist: delayed 00:23:00.243333 by SQLgrey-1.7.4 Received: from mail.totaltelecom.ro (mail.totaltelecom.ro [89.36.0.12]) by postgresql.org (Postfix) with ESMTP id 2E5459F9312 for ; Sat, 2 Dec 2006 15:24:10 -0400 (AST) Received: from mail.totaltelecom.ro (localhost [127.0.0.1]) by mail.totaltelecom.ro (Postfix) with ESMTP id 9417B34BE9A for ; Sat, 2 Dec 2006 21:01:03 +0200 (EET) Received: by mail.totaltelecom.ro (Postfix, from userid 99) id 8497134BEA6; Sat, 2 Dec 2006 21:01:03 +0200 (EET) Received: from alex (alex-vpnmobile.totaltelecom.ro [86.105.177.60]) by mail.totaltelecom.ro (Postfix) with ESMTP id 46A8E34BE9A for ; Sat, 2 Dec 2006 21:01:01 +0200 (EET) Message-ID: <00d901c71644$326b0490$3cb16956@alex> From: "Alexandru Coseru" To: Subject: Regex performance issue Date: Sat, 2 Dec 2006 21:00:49 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original 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-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200612/13 X-Sequence-Number: 22069 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 ------------------------------------------------------------------------------------------------------------------------------------ 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) The main problem is the query time. As you can see , the use of index destlist_indx2 is pretty quick (1.9 ms) , but the regexp operation takes a lot of time (857 ms). How can i improve that ? Regards Alex PS: Additional info: [root@voce1 billing]# uname -a Linux voce1 2.6.11-1.1369_FC4smp #1 SMP Thu Jun 2 23:16:33 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux [root@voce1 billing]# free total used free shared buffers cached Mem: 1023912 977788 46124 0 80900 523868 -/+ buffers/cache: 373020 650892 Swap: 3172728 8488 3164240 Welcome to psql 8.1.2, the PostgreSQL interactive terminal. asterisk=> \d destlist; Table "public.destlist" Column | Type | Modifiers ---------+------------------------+----------------------------------------------------------- id | bigint | not null default nextval(('destlist_id'::text)::regclass) id_ent | integer | dir | integer | prefix | character varying(255) | country | character varying(255) | network | character varying(255) | tip | integer | Indexes: "destlist_unique" UNIQUE, btree (id_ent, dir, prefix) "destlist_indx2" btree (id_ent, dir) "destlist_indx3" btree (id_ent, dir, prefix) "mmumu" btree (prefix varchar_pattern_ops) asterisk=> select count(*) from destlist; count -------- 576424 (1 row) [root@voce1 billing]# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 3.00GHz stepping : 3 cpu MHz : 2992.658 cache size : 2048 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 1 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr bogomips : 5914.62 clflush size : 64 cache_alignment : 128 address sizes : 36 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 3.00GHz stepping : 3 cpu MHz : 2992.658 cache size : 2048 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 1 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl cid cx16 xtpr bogomips : 5980.16 clflush size : 64 cache_alignment : 128 address sizes : 36 bits physical, 48 bits virtual power management: