CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
Ivan G.
Ivan G.

Reputation: 822

Would the query optimizer be able to extract an effective constant from a WHERE clause to avoid repeated evaluation?

Would I gain any performance benefit from rewriting a function that looks like this:

CREATE FUNCTION some_text_predicate(name text) RETURNS boolean AS $$
    SELECT EXISTS (SELECT 1 FROM sc.foo
        WHERE foo.name = some_transform(name)
    );
$$ LANGUAGE SQL;

as this:

CREATE FUNCTION some_text_predicate(text) RETURNS boolean AS $$
    DECLARE
        name text := some_transform($1);
    BEGIN
        RETURN EXISTS (SELECT 1 FROM sc.foo
            WHERE foo.name = name
        );
    END;
$$ LANGUAGE plpgsql;

The rationale being that the result of some_transform is a constant and doesn't need to be reevaluated on each row that the WHERE clause executes. Is the query optimizer smart enough to extract this as a constant or must I use the second function definition for the benefit I seek?

I'm looking for details on the optimization of the evaluation of WHERE clauses.

Upvotes: 1

Views: 78

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247215

It all depends on some_transform and the query.

If that function is IMMUTABLE (is guaranteed to return the same result for the same arguments) or STABLE (returns the same result for the same argument for the duration of the current query and does not modify the database), you can label your function the same.

Then the optimizer will know that the function only has to be evaluated once if the argument remains constant for the duration of the query.

An IMMUTABLE function will be evaluated when the query is planned, a STABLE function once at execution time.

That will help you with a query like

WHERE a.x = some_text_predicate('const')

but not with

WHERE some_text_predicate(a.x) = 'const'

I would prefer the first function if you can profit from that, because it is simpler.

If you cannot avoid the function being called often, the second function might be better, because PL/pgSQL functions cache execution plans. But you cannot avoid that the expensive function is called multiple times that way.

Upvotes: 1

Related Questions