From: Mitch Vincent <mitch@venux.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: LIKE and regex
Date: Thu, 18 May 2000 13:25:28 -0400
Message-ID: <01c101bfc0ee$0fe3ccc0$4100000a@venux.net> (raw)
References: <014b01bfc0e0$9fb352c0$4100000a@venux.net>
<24106.958667717@sss.pgh.pa.us>
> No, it's just a pretty brain-dead scanner. You can find the code in
> backend/utils/adt/like.c if you care.
I shall take a look, thanks!
> Perhaps it'd make sense to translate the LIKE pattern into an equivalent
> regex and then use the regex code, but no one's done it.
>
> > I'm trying to find the
> > quickest / fastest way to search for any occurance of a given string in
a
> > stored field.
> > query: select * from applicants where firstname LIKE '%mitch%';
> > query: select * from applicants where firstname ~ lower('mitch');
>
> I think the actual operator processing is going to be swamped by
> tuple-fetching overhead, so there's not going to be much difference.
> What you ought to be thinking about is how you could replace the
> brute-force scan with an index lookup. Possibly you could adapt the
> ideas in contrib/fulltextindex.
That's exactly what I'm doing (using ideas in contrib/fulltextindex) -- The
database is huge! (33 million rows right now) so it's taking quite some time
to do any operations on the database right now.. This is certainly to be
expected with as much data as there is stored in there.
It seems both LIKE and ~ uses the index I created, so that's all good...
I'm going to stick with the regex search I think, a co-worker was asking
about the LIKE operator, so I thought I would ask the guys that truely have
a clue!
Thanks again..
-Mitch
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, tgl@sss.pgh.pa.us
Subject: Re: LIKE and regex
In-Reply-To: <01c101bfc0ee$0fe3ccc0$4100000a@venux.net>
References: <014b01bfc0e0$9fb352c0$4100000a@venux.net> <24106.958667717@sss.pgh.pa.us> <01c101bfc0ee$0fe3ccc0$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