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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])