PostgreSQL Unused Indexes Test

While at one end indexes greatly enhance database performance, at the other they also add significant overhead to table change operations. Useless/unused indices can therefore be unnecessary resource hogs. Such indexes are typically not used by any regular query and may not enforce a constraint. However, these unneeded indexes cost you in several ways: they slow updates, inserts and deletes; they may keep HOT from updating the row in-place, requiring more VACUUMs; they take time to VACUUM; they add to query planning time; they take time to backup and restore. Administrators hence need to identify such indexes and eliminate them. The PostgreSQL Unused Indexes test helps administrators achieve the same. This test reports the number and names of unused/useless indexes, and thus prompts administrators to remove them so as to save the server from unnecessary performance degradations.

Target of the test : PostgreSQL server

Agent deploying the test: An internal/remote agent

Outputs of the test : One set of results for the target PostgreSQL server

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed

Host

The IP address of the host for which this test is to be configured.

Port

The port on which the server is listening. The default port is 5432.

Username

In order to monitor a PostgreSQL server, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is vested with the superuser privileges. The sample script we recommend for user creation for eG monitoring is:

CREATE ROLE eguser LOGIN



ENCRYPTED PASSWORD {‘eguser password’}



SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

Specify the name of this user in the Username text box.

Password

The password associated with the above Username (can be ‘NULL’). Here, ‘NULL’ means that the user does not have any password.

Confirm Password

Confirm the Password (if any) by retyping it here.

DB Name

The name of the database to connect to. The default is “postgres”.

Include DB

Specify a comma-separated list of databases that you wish to monitor in the Include DB text box.

Exclude DB

Specify a comma-separated list of databases that need to be excluded from monitoring in the Exclude DB text box. By default, this is set to rdsadmin.

Note:

If you are monitoring a PostgreSQL server hosted on AWS cloud, then make sure that you do not remove 'rdsadmin' from the Exclude DB list.

SSL

If the PostgreSQL server being monitored is an SSL-enabled server, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Number of indexes:

Indicates the number of indexes that are currently unused/useless on the server.

Number

A high value of this measure is a cause for concern. Use the detailed diagnosis of this measure to identify the unused indexes and take measures to get rid of them.