Skip site navigation (1) Skip section navigation (2)

Case sensitive searches

From: "Jeroen T(dot) Vermeulen" <jtv(at)xs4all(dot)nl>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Case sensitive searches
Date: 2002-06-27 10:05:21
Message-ID: 20020627100521.GE1678@bulletproof (view raw or flat)
Thread:
Lists: pgsql-hackers
I've just come across a case in Oracle 8.0.6 where important queries
could have been several orders of magnitude faster if only the optimizer
had realized that it was doing case-insensitive comparisons against a
constant that wasn't affected by case (a string of all digits).

The query was of the general form

	SELECT * FROM table
	WHERE upper(id) = '001234'

...causing a full index scan (there was a non-unique index on id).  What
the optimizer could perhaps have done was something like

	if (upper('001234') == lower('001234'))
		SELECT * FROM table
		WHERE id = '001234';
	else
		SELECT * FROM table
		WHERE upper(id) = '001234';

Even without the index I guess that would have saved it a lot of work.
In this case, of course, the user wasn't doing the smartest thing by
giving millions of records a numerical id but storing it as varchar.
OTOH there may also be a lot of cases like

	SELECT * FROM table
	WHERE upper(name) LIKE '%'

being generated by not-too-bright applications out there.

Does PostgreSQL do this kind of optimization?  If not, how easy and how
useful would it be to build it?  I suppose this sort of thing ought to
be in src/backend/optimizer/prep/ somewhere, but I couldn't find
anything like it.


Jeroen





pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2002-06-27 10:31:10
Subject: BETWEEN SYMMETRIC
Previous:From: Christopher Kings-LynneDate: 2002-06-27 09:40:00
Subject: mistake in sql99 compatibility?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group