Enterprisedb ' s index Advisor

The index recommendations are installed with PPAs to help determine which column on which table to index to improve performance under a given load.

The index suggestion and the PPAs query planner work together to calculate the execution cost by creating a "virtual" index.

There are two ways to use the index recommendation to parse SQL queries
A provides an index file containing the SQL statement, activating the index recommendation. The CREATE INDEX statement that generates the text.
b provides SQL statements in Edb-psql to let the index recommendations analyze.
It will try to provide suggestions on adding, deleting, checking and modifying statements.

When using explain or comparing the execution plan overhead when there is no associated index, if the associated index improves performance, the query plan for the time when there is no index is output.

First. index recommendation component
1
--/opt/postgresplus/9.1as/lib/plugins/plugin_index_advisor.so
/opt/postgresplus/9.2as/lib/index_advisor.so
--Win:
--Plugin_index_advisor.dll
2
There's a program under/opt/postgresplus/9.1as/.
Pg_advise_index
Win
Pg_advise_index.exe
3
:/opt/postgresplus/9.1as/share/contrib
Index_advisor.sql
The following objects are created to query the index recommendations:
CREATE TABLE Index_advisor_log (
Create or Replace function show_index_recommendations
Create or replace view index_recommendations as
The above object requires the creator to activate the index recommendation of the user to be able to access, query to the place
If the table index_advisor_log is not found, the proposed index is created in a temporary table with the same name.

Second. index suggestion configuration
No configuration is required for the current session, and table Index_advisor_log needs to be created for multiple session use.
A Configure Search_path=index_advisor_in_schema, and_old_values
b psql# \i/xxx/index_advisor.sql
Non-superuser users and the owners of these things need to give the table Index_advisor_log table on the increase, delete, check function.
And trying to index_recommendations on the search authority

$ edb-psql-d edb-u Enterprisedb
Edb-psql (9.0.0.6)
Type ' help ' for help.
edb=# CREATE SCHEMA ia;
CREATE SCHEMA
edb=# SET Search_path to IA;
SET
edb=# \i/opt/postgresplus/9.0as/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

Third. Use index recommendations
CREATE TABLE T (a int, b int);
INSERT into T SELECT S, 99999-s from Generate_series (0,99999) as S;
ANALYZE T;

1
Using the Pg_advise_index tool

1)
The establishment of the Workload.sql, the contents are as follows:
SELECT * FROM t WHERE a = 500;
SELECT * FROM T WHERE b < 1000;

2)
$./pg_advise_index-d edb-u enterprisedb-s 100m-o advisory.sql workload.sql
Poolsize = 102400 KB
Load workload from file ' Workload.sql '
Analyzing queries. Done.
Size = 2624 KB, benefit = 1684.720000
Size = 2624 KB, benefit = 1650.610000
/* 1. T ("a"): size=2624 KB, benefit=1684.72 * *
/* 2. T ("B"): size=2624 KB, benefit=1650.61 * *
/* Total size = 5248KB * *

3)
Create the generated index
$ edb-psql-d edb-h localhost-u enterprisedb-e-F Advisory.sql

2
Index recommendations in Psql

1)
Load Index Suggestion Plugin
edb=# LOAD ' $libdir/plugins/plugin_index_advisor ';
The proposal discovers all the SQL executed in this session and gives suggestions, and can add explain if you only want to analyze and don't want to execute these SQL statements.

2)
Executed in the Psql.
edb=# explain select * from t where a=100;
QUERY Plan

--------------------------------------------------------------------------------
--------
Seq Scan on t (cost=0.00..1693.00 Rows=1 width=8)
Filter: (A = 100)
Result (cost=0.00..8.28 Rows=1 width=8)
One-time Filter: ' ===[hypothetical Plan]=== ':: Text
-> Index Scan using "IDTH=8)
Index Cond: (A = 100)
(6 rows)

The comparison does not use the index recommendation:
edb=# explain select * from t where a=100;
QUERY Plan
----------------------------------------------------
Seq Scan on t (cost=0.00..1693.00 Rows=1 width=8)
Filter: (A = 100)
(2 rows)

3)
You can use the following command to stop and start the Index recommendation plug-in feature
edb=# show index_advisor.enabled;
edb=# set index_advisor.enabled= off;
edb=# set index_advisor.enabled= on;

4)
View the recommendations for the index recommendations.
There are three different ways:
A Run function show_index_recommendations
B View Table Index_advisor_log
C Query View Index_recommendations
A.
In this session, perform the following function to see the suggested index
edb=# Select show_index_recommendations (null);
B
View table Index_advisor_log See the relevant situation
The column benefit says: Calculated benefit of the index for this query
Benefit = (normal execution cost)-(execution cost with hypothetical index)
C
View backend_pid, and suggested index creation statements.
edb=# select * from Index_recommendations;
Backend_pid | Show_index_recommendations

-------------+------------------------------------------------------------------
----------------------------------------
4198 | Create INDEX idx_t_a on enterprisedb.t (a);/* size:2624 KB, BENEF
it:4707.28, gain:1.79393321711843 * *

Size = MAX (index size of all queries)
Benefit = SUM (benefit of each)
Gain = SUM (benefit of each)/MAX (index size to all queries)
Gain is useful when comparing different indexes to suggest which is better, and is used to indicate the revenue earned by unit disk consumption.

Fourth. restrictions
1 does not recommend multiple-column indexes
The 2 index recommendation ignores the formula in any WHERE clause, so the column in the recommended index is not an expression.
3 does not consider inherited inheritance, if you make recommendations on the parent table, no suggested indexes on any child tables are given.
4 when restoring pg_dump backed up files with Index_advisor_log, or when there are suggested indexes in the Index_advisor_log table,
There may be "broken links" because the OID of the table referenced by the rows in the table Index_advisor_log changes.
If you need to properly display the previous suggested index in table Index_advisor_log in the restored database, you need
UPDATE index_advisor_log SET reloid = new_oid WHERE reloid = old_oid;