Enforce query plan for queries with expensive predicates/UDFs

Question

I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find exactly one join partner in t2. Suppose we have the following query:

SELECT * 
FROM t1, 
     t2 
where t1.id = t2.id 
  AND expensiveFunc(t2.col_1, t2.col_2)

where expensiveFunc is a computationally heavy UDF written in Python (I know that Postgres has issues in determining the cost of such UDFs, lets keep this aside).

The optimizer suggests a plan where expensiveFunc is applied to all 100k rows of t2 right after the seq. scan and then the join between t1 and t2 is applied. However, since the UDF is very computationally expensive, the better strategy may be to first perform the join operation and then apply expensiveFunc only to the remaining 25k rows after the join. How can I enforce that the latter plan is performed? I was trying to enforce the order by using CASE and I also tried a CTE approach. However, both approaches create additional overhead which is not wanted. Are there certain hints I can give or other techniques to enforce/examine equivalent query plans.

Answer 1

If the function is immutable, you could create a partial index. Obviously the index creation and maintenance will be slow, but if the query is run far more often than the table is changed it could be worthwhile. In this case it would be something like:

create index on t2 (id) where expensiveFunc(col_1, col_2);

Setting the COST parameter for the UDF to a very high value will promote the use of this index even in cases where it might not otherwise be used.

If this approach doesn't work for some reason, then you can use a CTE to force its hand. In newer versions, you will need to specify it as materialized, otherwise it would still evaluate the function for each row, even if it does know it is slow. (The planner does not consider moving function evaluations around on plans which are otherwise identical. So the location of the evaluation is not a cost-based decision. It does know the cost is high, but that would only help if it had alternatives to compare it to, and it doesn't generate any alternatives which differ only in where the function is evaluated). If this "creates additional overhead" then you will need to decide if it is worth the extra overhead. If you showed us the real query and plan (add VERBOSE to the EXPLAIN as it is needed for this purpose to see where the function is evaluated) and explain how you measured the additional overhead, maybe we could help ameliorate that.