Thread: Case sensitive searches

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 tableWHERE 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
WHEREupper(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 tableWHERE 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

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

I'm no expert, but I can't image this will be easy, because the optimizer
does not know any relation between lower() and upper().
I think an index on upper(id) (create index idxname on table(upper(id)))
should work well.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org