The pg_collect_advice extension allows you to automatically generate plan advice each time a query is planned and store the query and the generated advice string either in local or shared memory. Note that this extension requires the pg_plan_advice module, which performs the actual plan advice generation; this module only knows how to store the generated advice for later examination. Whenever pg_collect_advice is loaded, it will automatically load pg_plan_advice.

In order to use this module, you will need to execute CREATE EXTENSION pg_collect_advice in at least one database, so that you have a way to examine the collected advice. You will also need the pg_collect_advice module to be loaded in all sessions where advice is to be collected. It will usually be best to do this by adding pg_collect_advice to shared_preload_libraries and restarting the server.

pg_collect_advice includes both a shared advice collector and a local advice collector. The local advice collector makes queries and their advice strings visible only to the session where those queries were planned, while the shared advice collector collects data on a system-wide basis, and authorized users can examine data from all sessions.

To enable a collector, you must first set a collection limit. When the number of queries for which advice has been stored exceeds the collection limit, the oldest queries and the corresponding advice will be discarded. Then, you must adjust a separate setting to actually enable advice collection. For the local collector, set the collection limit by configuring pg_collect_advice.local_collection_limit to a value greater than zero, and then enable advice collection by setting pg_collect_advice.local_collector = true. For the shared collector, the procedure is the same, except that the names of the settings are pg_collect_advice.shared_collection_limit and pg_collect_advice.shared_collector. Note that in both cases, query texts and advice strings are stored in memory, so configuring large limits may result in considerable memory consumption.

Once the collector is enabled, you can run any queries for which you wish to see the generated plan advice. Then, you can examine what has been collected using whichever of SELECT * FROM pg_get_collected_local_advice() or SELECT * FROM pg_get_collected_shared_advice() corresponds to the collector you enabled. To discard the collected advice and release memory, you can call pg_clear_collected_local_advice() or pg_clear_collected_shared_advice().

In addition to the query texts and advice strings, the advice collectors will also store the OID of the role that caused the query to be planned, the OID of the database in which the query was planned, the query ID, and the time at which the collection occurred. This module does not automatically enable query ID computation; therefore, if you want the query ID value to be populated in collected advice, be sure to configure compute_query_id = on. Otherwise, the query ID may always show as 0.