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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-06-27 10:31:10 | BETWEEN SYMMETRIC |
Previous Message | Christopher Kings-Lynne | 2002-06-27 09:40:00 | mistake in sql99 compatibility? |