Index Advisor works with EDB Postgres Advanced Server's query planner by creating hypothetical indexes that the query planner uses to calculate execution costs as if such indexes were available.
Prerequisites
BigAnimal Cluster type is EDB Postgres Advanced Server(Oracle Compatible).
Usage
Index Advisor is loaded in BigAnimal but it's not enabled globally
edb_admin=> show index_advisor.enabled ;
index_advisor.enabled
-----------------------
off
(1 row)
Step 1 set index_advisor.enabled to on
index_advisor can be enabled on different levels per your requirement.
-- transaction level
BEGIN;
SET LOCAL index_advisor.enabled = on;
-- ...
COMMIT;
-- session level
SET index_advisor.enabled = on;
-- user level
ALTER USER myuser SET index_advisor.enabled = on;
-- database level
ALTER DATABASE mydb SET index_advisor.enabled = on;
Step 2 Using Index Advisor at the psql command line
After index_advisor.enabled is set to on, then a temporary table index_advisor_log will be created.
Schema | Name | Type | Owner
-----------+-------------------------------------+-------+-----------
pg_temp_4 | index_advisor_log | table | edb_admin
In this example, the EXPLAIN statement displays the normal query plan. It's followed by the query plan of the same query if the query were using the recommended hypothetical index:
edb_admin=> EXPLAIN SELECT * FROM t WHERE a < 10000;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..3693.00 rows=9946 width=8)
Filter: (a < 10000)
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
-> Index Scan using """<hypothetical-index>:1""" on t (cost=0.42..454.79 rows=9946 width=8)
Index Cond: (a < 10000)
(6 rows)
Possible errors
- Superuser privilege is required to create the extension.
- If you created extension index_advisor (\dx can list all the installed extensions) and index_advisor.enabled is on, with the current user has no correct privileges on table index_advisor_log, then it may return the below error, grant the privileges to the user or turn off index_advisor.enabled will resolve the error (https://www.enterprisedb.com/docs/epas/latest/managing_performance/02_index_advisor/02_index_advisor_configuration/)
=> SELECT * FROM t WHERE a < 100;
ERROR: permission denied for table index_advisor_log
DETAIL: Index Advisor: "index_advisor_log" table is used to store recommendations.
You should have INSERT permissions on the table.
Also, Index advisor should not be run in a read-only transaction.
HINT: Index Advisor: "index_advisor_log" table can be created using the script index_advisor.sql.
CONTEXT: SQL statement "insert into index_advisor_log(reloid, relname, attrs, benefit, index_size, backend_pid, timestamp) values ( 16837, 16837::regclass::name, array[1], '3686.332520', 2624, 11537, now());"