Tom Lane wrote:
>
> I will bet lunch (at the nearest McD's, I'm not rich ;-)) that
> Vince Vielhaber's recent gripe about
> select city from locations where lower(city) = lower('st. ignace');
> failing to use an index
> create index locations_city on locations(lower(city) text_ops);
> is an artifact of the same sort of type-mismatch problem.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No. This is the result of using lower('st. ignace') - function:
optimizer considers clause as usable for index only for
constants and parameters!
We discussed this ~ month ago. lower('st. ignace') could be
replaced by parameter of PARAM_EXEC type (implemented
for subqueries) to be 1. considered by optimizer as index key
value, 2. evaluated _ONCE_ by executor.
As I mentioned before, lower('st. ignace') will be evaluated
FOR EACH tuple in SeqScan!!!
PARAM_EXEC was implemented to handle queries like this:
select * from A where A.x = (select max(B.y) from B)
- subquery will be executed once and index on A (x) will be
used (if exists).
Optimizer just rewrites this query as
select * from A where A.x = _parameter_
and stores information about _parameter_ in InitPlan of
execution plan.
Look:
vac=> explain select * from test where x = lower('a');
NOTICE: QUERY PLAN:
Seq Scan on test (cost=40.00 size=100 width=12)
^^^^^^^^
EXPLAIN
vac=> explain select * from test where x = (select lower('a'));
NOTICE: QUERY PLAN:
Index Scan using itest2 on test (cost=2.05 size=1 width=12)
^^^^^^^^^^
InitPlan
-> Result (cost=0.00 size=0 width=0)
Nevertheless,
vac=> explain select * from test where lower(x) = (select lower('a'));
NOTICE: IndexSelectivity: no key -1 in index 20305
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
NOTICE: QUERY PLAN:
Seq Scan on test (cost=40.00 size=100 width=12)
^^^^^^^^
InitPlan
-> Result (cost=0.00 size=0 width=0)
- something is broken for functional indices...
Vadim
