0

Query:

select * from table where col in ( values (?), (?), (?) )

The query text is generated, and number of (?) is variable and up to 10000.

It works just fine, just wondering, whether Postgres caches same plan instance for different number of args?

In other words: what is the key for plan cache in Postgres? And how does it apply for the query above?

1
  • 2
    Use col = ANY ($1) with an array. Then it will always be the same statement, and plans will be cached. Feb 23 at 7:25

2 Answers 2

1

Q: what is the key for plan cache in Postgres?

It's the prepared statement associated to the query if there is one, and it does not happen automatically. Outside of prepared statements, there is no plan cache.

From the documentation:

A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

Prepared statements can also be used at the protocol level by using the Parse/Bind/Execute flow, in which case whether your code uses them depends on the client-side driver and settings.

Even when using prepared statements, reuse of the plan depends on plan_cache_mode. That's because it's not necessarily good to reuse the same plan with different parameter values.

Queries like select * from table where id in ($1,$2) and select * from table where id in ($1,$2,$3) cannot be represented by the same prepared statement, so they have to be planned separately.

But select * from table where id=ANY($1) can be reused with different number of values expressed as a single array within the same prepared statements.

You can use EXPLAIN (ANALYZE, VERBOSE) EXECUTE name_of_prepared_statement(list-of-values) and look at Planning Time in the output to see how faster it is when the conditions are met for reusing the execution plan.

2

You change the number of params in query? Now you got a different query from the perspective of query planner. So in this case there won't be any reuse of execution plan(unless you use same num of params) as there can be 10000 execution plans as you change number of variables inside 'IN'. As a side note quoted identifiers "mytable" and "MYTABLE" is case-sensitive so there by it is different objects and yields different execution plans.

2
  • Are you sure about the side note? Feb 23 at 7:24
  • @LaurenzAlbe yeah realised just now and edited, I was meant to talk about quoted identifiers. Thanks for highlighting.
    – mediocre
    Feb 23 at 9:11

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.