pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mitch Vincent <mitch@venux.net>
To: pgsql-sql@postgresql.org
Subject: LIKE and regex
Date: Thu, 18 May 2000 11:49:17 -0400
Message-ID: <014b01bfc0e0$9fb352c0$4100000a@venux.net> (raw)

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."






view thread (3+ messages)  latest in thread

Message-ID: <014b01bfc0e0$9fb352c0$4100000a@venux.net>
Permalink:  ../014b01bfc0e0$9fb352c0$4100000a@venux.net/
Also on:    postgresql.org/message-id/014b01bfc0e0$9fb352c0$4100000a@venux.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mitch@venux.net
  Subject: Re: LIKE and regex
  In-Reply-To: <014b01bfc0e0$9fb352c0$4100000a@venux.net>
  References: <014b01bfc0e0$9fb352c0$4100000a@venux.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox