0

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.

4
  • 1
    Please provide the version of PostgreSQL in use, and if you can, the output of EXPLAIN ANALYZE for the query with the poor plan.
    – dwhitemv
    Nov 28, 2022 at 20:06
  • 1
    Please include the execution plans (explain (analyze, buffers)) of all the alternatives you have tried as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan.
    – user1822
    Nov 28, 2022 at 20:31
  • 1
    The "both approaches create additional overhead which is not wanted" seems to indicate that the query is not as simply as the one you have shown us.
    – user1822
    Nov 28, 2022 at 20:32
  • Are there any FOREIGN KEY constraints between the two tables? It would be good to add the CREATE TABLE statements for both tables (including indexes) and the actual query - with EXPLAIN output, as suggested above. Nov 29, 2022 at 8:07

1 Answer 1

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.

1
  • Thanks for your great answer, both of your suggestions (index and CTE) worked out great. Especially when using a CTE the results were as I wanted!
    – KSV97
    Nov 30, 2022 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.