In this lesson, you will learn how to influence the PostgreSQL query planner by assigning custom execution costs to your user-defined operators.
Why Cost Matters
The PostgreSQL query planner is a cost-based optimizer; it calculates the "price" of every potential execution path by summing the CPU and I/O costs of each operator. By default, the planner assumes every operator is relatively cheap. However, if you create a computationally expensive custom function—such as one performing complex regex, cryptographic hashing, or an API call—the planner might mistakenly choose an inefficient plan (like a Nested Loop) because it underestimates the effort required for that function.
Assigning Custom Costs
When you define a function in PostgreSQL using CREATE FUNCTION, you can specify a COST parameter. This integer represents the estimated execution cost in units of cpu_operator_cost. If you set this value high, the planner will treat the operator as "expensive," forcing it to favor plans that minimize the number of times that function is called.
Consider a scenario where you have a function heavy_analysis(text) that takes significant CPU time:
CREATE FUNCTION heavy_analysis(val text) RETURNS boolean
AS '...'
LANGUAGE sql
COST 1000; -- Default is 1, so 1000 tells the planner this is 1000x more expensive
If you execute SELECT * FROM data WHERE heavy_analysis(my_column), the planner will see that cost of 1000. If it were choosing between a sequential scan and an index scan, the high cost of the function might discourage the planner from using an index if it calculates that the index would require calling this "heavy" function too many times, or conversely, it may prompt the planner to perform a filter at the latest possible stage of the plan to avoid overhead.
By providing these hints, you move the optimizer away from generic assumptions and toward a strategy that accounts for the reality of your data processing workload. Always monitor EXPLAIN ANALYZE outputs after modifying costs to ensure the planner is making decisions that actually reduce your total execution time.
Key Takeaway: Use the COST parameter in function definitions to notify the query planner of expensive operations, preventing suboptimal plan choices that arise from default estimation assumptions.