The pg_stash_advice extension allows you to stash plan advice strings in dynamic shared memory where they can be automatically applied. An advice stash is a mapping from query identifiers to plan advice strings. Whenever a session is asked to plan a query whose query ID appears in the relevant advice stash, the plan advice string is automatically applied to guide planning. Note that advice stashes exist purely in memory. This means both that it is important to be mindful of memory consumption when deciding how much plan advice to stash, and also that advice stashes must be recreated and repopulated whenever the server is restarted.

In order to use this module, you will need to execute CREATE EXTENSION pg_stash_advice in at least one database, so that you have access to the SQL functions to manage advice stashes. You will also need the pg_stash_advice module to be loaded in all sessions where you want this module to automatically apply advice. It will usually be best to do this by adding pg_stash_advice to shared_preload_libraries and restarting the server.

Once you have met the above criteria, you can create advice stashes using the pg_create_advice_stash function described below and set the plan advice for a given query ID in a given stash using the pg_set_stashed_advice function. Then, you need only configure pg_stash_advice.stash_name to point to the chosen advice stash name. For some use cases, rather than setting this on a system-wide basis, you may find it helpful to use ALTER DATABASE ... SET or ALTER ROLE ... SET to configure values that will apply only to a database or only to a certain role. Likewise, it may sometimes be better to set the stash name in a particular session using SET.

Because pg_stash_advice works on the basis of query identifiers, you will need to determine the query identifier for each query whose plan you wish to control. You will also need to determine the advice string that you wish to store for each query. One way to do this is to use EXPLAIN: the VERBOSE option will show the query ID, and the PLAN_ADVICE option will show plan advice. pg_collect_advice can be used to obtain this information for an entire workload, although care must be taken since it can use up a lot of memory very quickly. Query identifiers can also be obtained through tools such as pg_stat_statements or Section 27.2.3, but these tools will not provide plan advice strings. Note that compute_query_id must be enabled for query identifiers to be computed; if set to auto, loading pg_stash_advice will enable it automatically.

Generally, the fact that the planner is able to change query plans as the underlying distribution of data changes is a feature, not a bug. Moreover, applying plan advice can have a noticeable performance cost even when it does not result in a change to the query plan. Therefore, it is a good idea to use this feature only when and to the extent needed. Plan advice strings can be trimmed down to mention only those aspects of the plan that need to be controlled, and used only for queries where there is believed to be a significant risk of planner error.

Note that pg_stash_advice currently lacks a sophisticated security model. Only the superuser, or a user to whom the superuser has granted EXECUTE permission on the relevant functions, may create advice stashes or alter their contents, but any user may set pg_stash_advice.stash_name for their session, and this may reveal the contents of any advice stash with that name. Users should assume that information embedded in stashed advice strings may become visible to nonprivileged users.