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@molbio.uoregon.edu
