Index Advisor Configuration v13
Index Advisor does not require configuration to generate recommendations that are available only for the duration of the current session; to store the results of multiple sessions, you must create the index_advisor_log
table (where Advanced Server will store Index Advisor recommendations). To create the index_advisor_log
table , you must run the index_advisor.sql
script.
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 the Index Advisor.
Place the selected schema at the start of your
search_path
parameter. For example, if your search path is currently:and you want the Index Advisor objects to be created in a schema named
advisor
, use the command:Create the
index_advisor
extension, which creates the database objects. Connect to the database as the database superuser using psql, and enter the command:Note
If you're using Index Advisor in an earlier version of EDB Postgres Advanced Server and upgrading to the latest version, 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_log
table to all Index Advisor users; this step is not necessary if the Index Advisor user is a superuser, or the owner of these database objects.- Grant
SELECT
andINSERT
privileges on theindex_advisor_log
table to allow a user to invoke Index Advisor. - Grant
DELETE
privileges on theindex_advisor_log
table to allow the specified user to delete the table contents. - Grant
SELECT
privilege on theindex_recommendations
view.
- Grant
The following example demonstrates the creation of the Index Advisor database objects in a schema named ia
, which will then be accessible to an Index Advisor user with user name ia_user
:
While using Index Advisor, the specified schema (ia)
must be included in ia_user
's search_path
parameter.