Configuring the Index Advisor v15
Index Advisor doesn't require configuration to generate recommendations that are available only for the rest of the current session. To store the results of multiple sessions, you must create the
index_advisor_log table, where EDB Postgres Advanced Server stores Index Advisor recommendations. To create the
index_advisor_log table, create the extension
When selecting a storage schema for the Index Advisor table, function, and view, keep in mind that all users that invoke Index Advisor and query the result set must have USAGE privileges on the schema. The schema must be in the search path of all users that are interacting with Index Advisor.
Place the selected schema at the start of your
search_pathparameter. For example, suppose your search path is currently:
If you want to create the Index Advisor objects in a schema named
advisor, use the command:
index_advisorextension, which creates the database objects. Connect to the database as the database superuser using psql, and enter the command:
If you're using Index Advisor in an earlier version of EDB Postgres Advanced Server and upgrading to version 15, then use this command to create the index_advisor extension:
This command creates the extension and links any of the old database objects to it.
Grant privileges on the
index_advisor_logtable to all Index Advisor users. This step isn't necessary if the Index Advisor user is a superuser or the owner of these database objects.
INSERTprivileges on the
index_advisor_logtable to allow a user to invoke Index Advisor.
DELETEprivileges on the
index_advisor_logtable to allow the specified user to delete the table contents.
SELECTprivilege on the
This example shows creating Index Advisor database objects in a schema named
ia. The schema is accessible to an Index Advisor user with user name
While using Index Advisor, the specified schema
(ia) must be included in the