From: | "Mitch Vincent" <mitch(at)venux(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | LIKE and regex |
Date: | 2000-05-18 15:49:17 |
Message-ID: | 014b01bfc0e0$9fb352c0$4100000a@venux.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I'm wondering if LIKE uses regex internally.. I'm trying to find the
quickest / fastest way to search for any occurance of a given string in a
stored field.
I tried both of these :
query: select * from applicants where firstname LIKE '%mitch%';
ProcessQuery
! system usage stats:
! 0.407890 elapsed 0.104924 user 0.139892 system sec
! [0.133769 user 0.149507 sys total]
! 6/13 [7/13] filesystem blocks in/out
! 0/60 [0/425] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent
! 6/15 [8/26] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 1301 read, 0 written, buffer hit rate
= 41.21%
! Local blocks: 0 read, 0 written, buffer hit rate
= 0.00%
! Direct blocks: 0 read, 0 written
query: select * from applicants where firstname ~ lower('mitch');
ProcessQuery
! system usage stats:
! 0.234621 elapsed 0.101358 user 0.124995 system sec
! [0.287402 user 0.242496 sys total]
! 0/5 [160/10] filesystem blocks in/out
! 0/0 [0/434] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [4/4] messages rcvd/sent
! 0/35 [153/55] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 1243 read, 0 written, buffer hit rate
= 0.24%
! Local blocks: 0 read, 0 written, buffer hit rate
= 0.00%
! Direct blocks: 0 read, 0 written
While there is quite a load on that box right now (doing a big CLUSTER) I
tried the above queries 3 times, the above is the result of both 3rd-round
queries.. It looks like LIKE is faster to me.. Any comments?
Thanks!
- Mitch
"The only real failure is quitting."
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Guryanow | 2000-05-18 16:22:01 | indecies are not used by '<=' operator on varchar fields |
Previous Message | Bruce Momjian | 2000-05-18 15:44:26 | Re: Re[2]: lower() for varchar data by creating an index |