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.
index _advisor will be replaced by Query_Advisor starting from v16. EDB Docs - Using EDB Query Advisor
Prerequisites
EDB Postgres AI Cluster type is EDB Postgres Advanced Server(Oracle Compatible).
Usage
Index Advisor is loaded in EDB Postgres AI 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)
Below is an example
By checking the temp table index_advisor_log, the 3rd column needs an index.
edb_admin=# table index_advisor_log ;
reloid | relname | attrs | benefit | index_size | backend_pid | timestamp
--------+---------+-------+-----------+------------+-------------+----------------------------------
16824 | a | {3} | 872655.94 | 781960 | 4846 | 12-DEC-23 08:30:58.942825 +00:00
(3 rows)
Check the table structure, so we can know the column is c2.
edb_admin=# \d a
Table "public.a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-------------------------------
id | integer | | not null | nextval('a_id_seq'::regclass)
c1 | integer | | |
c2 | integer | | |
c6 | text | | |
Indexes:
"a_pkey" PRIMARY KEY, btree (id)
edb_admin=# create index id_a_c2 on a(c2);
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());"