dexter

# Dexter

The automatic indexer for Postgres

[Read about how it works](https://ankane.org/introducing-dexter) or [watch the talk](https://www.youtube.com/watch?v=Mni_1yTaNbE)

[![Build Status](https://github.com/ankane/dexter/actions/workflows/build.yml/badge.svg)](https://github.com/ankane/dexter/actions)

## Installation

First, install [HypoPG](https://github.com/HypoPG/hypopg) on your database server. This doesn’t require a restart.

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

And enable it in databases where you want to use Dexter:

```sql
CREATE EXTENSION hypopg;
```

See the [installation notes](#hypopg-installation-notes) if you run into issues.

Then install the command line tool with:

```sh
gem install pgdexter
```

The command line tool is also available with [Docker](#docker) or [Homebrew](#homebrew).

## How to Use

Dexter needs a connection to your database and a source of queries (like [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html)) to process.

```sh
dexter -d dbname --pg-stat-statements
```

This finds slow queries and generates output like:

```
Started
Processing 189 new query fingerprints
Index found: public.genres_movies (genre_id)
Index found: public.genres_movies (movie_id)
Index found: public.movies (title)
Index found: public.ratings (movie_id)
Index found: public.ratings (rating)
Index found: public.ratings (user_id)
```

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

```
Index found: public.ratings (user_id)
Creating index: CREATE INDEX CONCURRENTLY ON "public"."ratings" ("user_id")
Index created: 15243 ms
```

## Connection Options

Dexter supports the same connection options as psql.

```
-h host -U user -p 5432 -d dbname
```

This includes URIs:

```
postgresql://user:pass@host:5432/dbname
```

and connection strings:

```
host=localhost port=5432 dbname=mydb
```

Always make sure your [connection is secure](https://ankane.org/postgres-sslmode-explained) when connecting to a database over a network you don’t fully trust.

## Collecting Queries

Dexter can collect queries from a number of sources.

- [Query stats](#query-stats)
- [Live queries](#live-queries)
- [Log files](#log-files)
- [SQL files](#sql-files)

### Query Stats

Enable [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) in your database.

```psql
CREATE EXTENSION pg_stat_statements;
```

And use:

```sh
dexter <connection-options> --pg-stat-statements
```

### Live Queries

Get live queries from the [pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW) view with:

```sh
dexter <connection-options> --pg-stat-activity
```

### Log Files

Enable logging for slow queries in your Postgres config file.

```ini
log_min_duration_statement = 10 # ms
```

And use:

```sh
dexter <