Dexter

The automatic indexer for Postgres

Read about how it works

Installation

First, install HypoPG on your database server. This doesn’t require a restart.

wget https://github.com/dalibo/hypopg/archive/1.0.0.tar.gz
tar xf 1.0.0.tar.gz
cd hypopg-1.0.0
make
make install # may need sudo

Note: If you have issues, make sure postgresql-server-dev-* is installed.

Enable logging for slow queries in your Postgres config file.

log_min_duration_statement = 10 # ms

And install the command line tool with:

gem install pgdexter

The command line tool is also available as a Linux package.

How to Use

Dexter needs a connection to your database and a log file to process.

tail -F -n +1 <log-file> | dexter <database-url>

This finds slow queries and generates output like:

2017-06-25T17:52:19+00:00 Started
2017-06-25T17:52:22+00:00 Processing 189 new query fingerprints
2017-06-25T17:52:22+00:00 Index found: genres_movies (genre_id)
2017-06-25T17:52:22+00:00 Index found: genres_movies (movie_id)
2017-06-25T17:52:22+00:00 Index found: movies (title)
2017-06-25T17:52:22+00:00 Index found: ratings (movie_id)
2017-06-25T17:52:22+00:00 Index found: ratings (rating)
2017-06-25T17:52:22+00:00 Index found: ratings (user_id)
2017-06-25T17:53:22+00:00 Processing 12 new query fingerprints

To be safe, Dexter will not create indexes unless you pass the --create flag. In this case, you’ll see:

2017-06-25T17:52:22+00:00 Index found: ratings (user_id)
2017-06-25T17:52:22+00:00 Creating index: CREATE INDEX CONCURRENTLY ON "ratings" ("user_id")
2017-06-25T17:52:37+00:00 Index created: 15243 ms

Single Statement Mode

You can also pass a single statement with:

dexter <database-url> -s "SELECT * FROM ..."

Options

Name	Description	Default
exclude	prevent specific tables from being indexed	None
interval	time to wait between processing queries, in seconds	60
log-level	debug gives additional info for suggested indexes debug2 gives additional info for processed queries	info
log-sql	log SQL statements executed	false
min-time	only process queries consuming a min amount of DB time, in minutes	0

Future Work

Here are some ideas

Thanks

This software wouldn’t be possible without HypoPG, which allows you to create hypothetical indexes, and pg_query, which allows you to parse and fingerprint queries. A big thanks to Dalibo and Lukas Fittl respectively.

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help:

• Report bugs
• Fix bugs and submit pull requests
• Write, clarify, or fix documentation
• Suggest or add new features

To get started, run:

git clone https://github.com/ankane/dexter.git
cd dexter
bundle
rake install

To run tests, use:

rake test