From: Tom Lane <tgl@sss.pgh.pa.us>
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Searching Text Fields - Case Sensitive?
Date: Fri, 13 Aug 1999 10:27:15 -0400
Message-ID: <4269.934554435@sss.pgh.pa.us> (raw)
In-Reply-To: <002801bee560$2e499d30$760e01a3@oucs.ox.ac.uk>
"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
>> SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%";
> Brings up an interesting question. Which is faster, to use the
> lower/upper functions as above (and of course if foobar is a variable,
> it would also have to be made lower case by the program calling
> pgsql), or to use the pattern matching case-insensitive operator?
Offhand I'd guess that the case-insensitive regex would be faster than
the above, because it avoids the overhead of making an extra function
call. (Functions that return text, or any other variable-length data
type, require a memory allocation step --- for typical string lengths
I'd imagine the malloc costs more than the actual character-slinging...)
*However*, the situation changes considerably if you have an index
on lower(description) and a pattern that allows the index to be used.
The above pattern does not, but any left-anchored pattern, say
'foobar%', would allow index restriction clauses to be generated and
used. The effective query with a left-anchored pattern is like this:
... WHERE lower(description) LIKE "foobar%" AND
lower(description) >= "foobar" AND
lower(description) < "foobas";
(actually there are some character-set issues that complicate matters,
but that's the basic idea). When you have an index on
lower(description), the system can and will use the last two clauses
to restrict an indexscan so that tuples outside the range foobar to
foobas are never even fetched. Obviously, this can make for a speedup
of orders of magnitude, swamping any nitpicky little questions of how
fast a particular expression can be evaluated.
If you use a case-insensitive regex then this doesn't work, even for a
left-anchored pattern ('^foobar'), because the system knows that 'f' can
match either 'f' or 'F' so it can't generate the index restriction
clause...
regards, tom lane
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: tgl@sss.pgh.pa.us, moray.mcconnachie@computing-services.oxford.ac.uk
Subject: Re: [SQL] Searching Text Fields - Case Sensitive?
In-Reply-To: <4269.934554435@sss.pgh.pa.us>
References: <002801bee560$2e499d30$760e01a3@oucs.ox.ac.uk> <4269.934554435@sss.pgh.pa.us>
* 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