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?
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.
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.
col = ANY ($1)with an array. Then it will always be the same statement, and plans will be cached.