The automatic indexer for Postgres

Using Ankane: Dexter you can find missing indexes in your database.

You will need to install HypoPG - PostgreSQL extension for hypothetical indexes too.

After installation using instruction in repo. You need to provide query logs to execute them with dexter. It will analyse and inform you if any additional index should be created.

Installation:

cd /tmp
curl -L https://github.com/HypoPG/hypopg/archive/1.3.1.tar.gz | tar xz
cd hypopg-1.3.1
make
make install # may need sudo
gem install pgdexter

Next, enable in postgres this setting (it will be -1 - never by default) :

log_min_duration_statement = 10 # ms

(after turining this on you will need restart porstgresql, after that, launch your application, and make standard operations queries etc. it will gather statements data to logs)

After that, you will have prepared .log file with queries like select * .... where (id=2) - Yes it will write all statements with values, it is required for best analyze.

File should be available somewhere /var/log/postgresql/....

Next just use:

tail -F -n +1 <log-file> | dexter <connection-options>

And then after few seconds it will analyse all historyical selections

By default, indexes will not be created on your database. It will only log in the console, for example:

 Creating index: CREATE INDEX CONCURRENTLY ON user (id)

For fully instuction read this docs: Docs

source from ankane: dexter docs