Re: [HACKERS] indexes and floats

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