Case sensitive searches
От | Jeroen T. Vermeulen |
---|---|
Тема | Case sensitive searches |
Дата | |
Msg-id | 20020627100521.GE1678@bulletproof обсуждение исходный текст |
Список | 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 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
В списке pgsql-hackers по дате отправления: