LIKE vs regex queries

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

Responses

Browse pgsql-hackers by date

  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

Browse pgsql-patches by date

  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