Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-postgresql-cache-statements-and-data
The question I have is what will destroy/re-write/clear postgres has cached? Naturally, any DDL statement (such as drop, alter etc.) would do. But are there other things?
Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment:
* Currently, we track exactly the dependencies of plans on relations and
* user-defined functions. On relcache invalidation events or pg_proc
* syscache invalidation events, we invalidate just those plans that depend
* on the particular object being modified. (Note: this scheme assumes
* that any table modification that requires replanning will generate a
* relcache inval event.) We also watch for inval events on certain other
* system catalogs, such as pg_namespace; but for them, our response is
* just to invalidate all plans. We expect updates on those catalogs to
* be infrequent enough that more-detailed tracking is not worth the effort.
In the case of tables:
git grep CacheInvalidateRelcache
on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:
REINDEXCLUSTER and VACUUM (FULL)ALTER TABLETRUNCATEOther actions that lead to any such action will of course also invalidate plans.
