SQL COST

What is the SQL COST clause?

Assigns an estimated execution cost to a user defined function or operator so the PostgreSQL query planner can choose optimal plans.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COST: Supported: PostgreSQL 7.4+. Not supported: MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery.

SQL COST Full Explanation

In PostgreSQL, the COST clause is used inside CREATE FUNCTION, ALTER FUNCTION, CREATE OPERATOR and ALTER OPERATOR statements. It lets the author tell the query planner how expensive a call to the function (or operator) is relative to a simple operator such as an integer addition, which has a default cost of 1.0. The planner multiplies the cost by the estimated number of calls when comparing execution plans, so a higher COST discourages use of the function in performance-critical sections like WHERE filters or JOIN conditions. The value is a floating-point number measured in arbitrary cost units; it should grow with CPU time, memory usage, and possible I/O done by the function. PostgreSQL assigns defaults of 100 for functions written in languages that can access the database (plpgsql, SQL) and 1000 for functions marked as VOLATILE and written in C, but explicit COST lets you override these heuristics. Caveats:- COST affects planning only, not execution. A wrong estimate may cause poor plans.- Only superusers or the function owner can set or change COST.- The clause has no effect in other SQL dialects.

SQL COST Syntax

-- Assign cost at creation
CREATE FUNCTION schema.add_with_log(a int, b int)
RETURNS int
LANGUAGE plpgsql
COST 50
AS $$
BEGIN
  INSERT INTO add_log VALUES (a, b, now());
  RETURN a + b;
END;
$$;

-- Change cost later
ALTER FUNCTION schema.add_with_log(int, int)
COST 10;

SQL COST Parameters

cost_value (numeric) - Positive floating point number representing relative expense; higher means more expensive.

Example Queries Using SQL COST

-- Example 1: Expensive function avoided in WHERE
CREATE FUNCTION heavy_compute(x int)
RETURNS int LANGUAGE plpgsql COST 500 AS $$ BEGIN RETURN x * x; END; $$;

EXPLAIN SELECT * FROM nums WHERE heavy_compute(id) < 100;  -- Planner likely avoids this filter early

-- Example 2: Reducing cost to encourage use
ALTER FUNCTION heavy_compute(int) COST 5;
EXPLAIN SELECT * FROM nums WHERE heavy_compute(id) < 100;  -- Now planner may push the function into the plan

Expected Output Using SQL COST

The CREATE or ALTER statement returns CREATE FUNCTION or ALTER FUNCTION
Subsequent EXPLAIN plans use the specified cost when estimating total query cost, potentially changing join order and filter placement

Use Cases with SQL COST

Tuning planner decisions when a function is cheaper or costlier than the default assumption.
Preventing an expensive, side-effecting function from being executed repeatedly by setting a high cost.
Encouraging inlining of lightweight SQL or immutable C functions by assigning a very low cost.

Common Mistakes with SQL COST

Forgetting that COST does not auto-adjust if the function body changes.
Setting an unrealistically low cost for heavy functions, leading to slow queries.
Assuming COST affects runtime directly; it only influences the planner.
Specifying COST in databases other than PostgreSQL where the clause is unsupported.

First Introduced In

PostgreSQL 7.4

Frequently Asked Questions

What does the COST clause do?

COST tells the PostgreSQL planner how expensive a function or operator call is relative to a simple operation. The planner uses it when comparing execution plans.

Is COST required when creating a function?

No. PostgreSQL supplies default costs (100 or 1000) based on language and volatility. Explicit COST is optional but useful for fine-tuning.

How do I pick a good COST value?

Benchmark the function with EXPLAIN ANALYZE, then scale the cost so the planner's estimates align with real runtime. Start small and iterate.

Can COST worsen performance?

Yes. An inaccurate COST may mislead the planner into choosing suboptimal plans, increasing total query time.