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:
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.