Awhile back I said:
> [ SQL-function inlining breaks planner's ability to recognize functional
>   indexes ]

> The urge is becoming very strong to fix this by replacing functional
> indexes with expressional indexes, as I suggested doing many moons ago.
> Then the difficulty at hand would be easily handled by running the
> index's defining expression through eval_const_expressions before
> checking if it is equal() to a candidate WHERE argument.  Not sure I
> have time to do that before 7.4 though :-(

I've now done this (amazing what you can do when you're putting off
editing thousands of elog() calls ...), and so Stephan's example
works again:

regression=# explain select * from b1 where (fold_clients(a,b))='1_A';
                              QUERY PLAN
 Index Scan using b1ind on b1  (cost=0.00..17.12 rows=5 width=36)
   Index Cond: ((((a)::text || '_'::text) || upper(b)) = '1_A'::text)
(2 rows)

Note that EXPLAIN will show the index condition in terms of the inlined
function's expansion.  A rather interesting property of this behavior
is that the index can get matched to whether you write the original
function or its equivalent expression.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

Reply via email to