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? http://www.postgresql.org/docs/faqs/FAQ.html