From: | Dan Graham <graham(at)molbio(dot)uoregon(dot)edu> |
---|---|
To: | <pgsql-patches(at)postgresql(dot)org> |
Subject: | LIKE vs regex queries |
Date: | 2004-04-07 01:35:22 |
Message-ID: | Pine.LNX.4.33.0404061726500.21668-100000@molbio.uoregon.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
I have a database with about 250,000 entries in a table, PG 7.5, One
of the fields is text. LIKE queries on this field execute much faster
than the equivalent regex queries.
Is this what you would expect? Should I prefer LIKE to regex?
(I'm a regex fan, but the performance hit seems steep.)
I've pasted sample output in below.
================================================================
order=# explain select dnum from item where description LIKE '%Ushio%';
QUERY PLAN
----------------------------------------------------------
Seq Scan on item (cost=0.00..7330.40 rows=349 width=16)
Filter: (description ~~ '%Ushio%'::text)
(2 rows)
order=# select dnum from item where description LIKE '%Ushio%';
dnum
--------------
B521479
MB105921
MB109239
MB110491
MB111390
MB111983
MB112854
MB115020
MB115020
MB120247
MB121532
(11 rows)
Time: 855.540 ms
==================================================================
order=# explain select dnum from item where description ~ 'Ushio';
QUERY PLAN
----------------------------------------------------------
Seq Scan on item (cost=0.00..7330.40 rows=349 width=16)
Filter: (description ~ 'Ushio'::text)
(
order=# select dnum from item where description ~ 'Ushio';
dnum
--------------
B521479
MB105921
MB109239
MB110491
MB111390
MB111983
MB112854
MB115020
MB115020
MB120247
MB121532
(11 rows)
Time: 2409.043 ms
=========================================================================
Daniel Graham
graham(at)molbio(dot)uoregon(dot)edu
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Swan | 2004-04-07 01:44:58 | Re: Function to kill backend |
Previous Message | Claudio Natoli | 2004-04-07 01:02:48 | Re: Small suggestion on build script |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Swan | 2004-04-07 01:44:58 | Re: Function to kill backend |
Previous Message | Andrew Dunstan | 2004-04-06 22:09:36 | Re: [PATCHES] logging statement levels |