Index Advisor Configuration v10

Suggest edits

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.

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

    search_path=public, accounting

    and you want the Index Advisor objects to be created in a schema named advisor, use the command:

    SET search_path = advisor, public, accounting;

  2. Run the index_advisor.sql script to create the database objects. If you are running the psql client, you can use the command:

    \i full_pathname/index_advisor.sql

    Specify the pathname to the index_advisor.sql script in place of full_pathname.

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

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:

$ edb-psql -d edb -U enterprisedb
edb-psql (10.0.1)
Type "help" for help.

edb=# CREATE SCHEMA ia;
CREATE SCHEMA
edb=# SET search_path TO ia;
SET
edb=# \i /opt/edb/as10/share/contrib/index_advisor.sql
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
edb=# GRANT USAGE ON SCHEMA ia TO ia_user;
GRANT
edb=# GRANT SELECT, INSERT, DELETE ON index_advisor_log TO ia_user;
GRANT
edb=# GRANT SELECT ON index_recommendations TO ia_user;
GRANT

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

← Prev

Index Advisor Components

Next →

Using Index Advisor