Index Advisor configuration v14

Index Advisor doesn't require configuration to generate recommendations that are available only for 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, 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 Index Advisor.

1. Place the selected schema at the start of your search_path parameter. For example, suppose your search path is currently:

   Toggle WrapCopy

   search_path=public, accounting

   If you want to create the Index Advisor objects in a schema named advisor, use the command:

   Toggle WrapCopy

   SET search_path = advisor, public, accounting;

2. Create the index_advisor extension, which creates the database objects. Connect to the database as the database superuser using psql, and enter the command:

   Toggle WrapCopy

   # running as the database superuser using psql
   create extension index_advisor ;

   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:

   Toggle WrapCopy

   # running as the database superuser using psql
   create extension index_advisor version 1.1;

   This command creates the extension and links any of the old database objects to it.

3. Grant privileges on the index_advisor_log table 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.

   • Grant SELECT and INSERT privileges on the index_advisor_log table to allow a user to invoke Index Advisor.
   • Grant DELETE privileges on the index_advisor_log table to allow the specified user to delete the table contents.
   • Grant SELECT privilege on the index_recommendations view.

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 ia_user.

# running as enterprisedb user 
edb-psql -d edb -U enterprisedb

# running commands inside psql
CREATE SCHEMA ia;
SET search_path TO ia;
CREATE EXTENSION index_advisor;
GRANT USAGE ON SCHEMA ia TO ia_user;
GRANT SELECT, INSERT, DELETE ON index_advisor_log TO ia_user;
GRANT SELECT ON index_recommendations TO ia_user;

While using Index Advisor, the specified schema (ia) must be included in the ia_user search_path parameter.