Regex performance issue - Mailing list pgsql-performance

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: