pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Alexandru Coseru <alexandru.coseru@totaltelecom.ro>
To: pgsql-performance@postgresql.org
Subject: Regex performance issue
Date: Sat, 2 Dec 2006 21:00:49 +0200
Message-ID: <00d901c71644$326b0490$3cb16956@alex> (raw )
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:
view thread (13+ messages) latest in thread
Message-ID: <00d901c71644$326b0490$3cb16956@alex>
Permalink: ../00d901c71644$326b0490$3cb16956@alex/
Also on: postgresql.org/message-id/00d901c71644$326b0490$3cb16956@alex
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alexandru.coseru@totaltelecom.ro
Subject: Re: Regex performance issue
In-Reply-To: <00d901c71644$326b0490$3cb16956@alex>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox