Dexter

Dexter analyzes queries to find missing indexes. By default, it only identifies potential indexes without creating them. To actually apply the changes, you must use the --create flag.

Find indexes (Dry Run):

dexter -d dbname --pg-stat-statements

Create indexes:

dexter -d dbname --pg-stat-statements --create

Dexter requires the hypopg extension to be installed on your Postgres database server before installing the Dexter CLI.

1. Install HypoPG

Download, compile, and install HypoPG on your database server:

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

Then, enable it in your database:

CREATE EXTENSION hypopg;

2. Install Dexter CLI

Install the command line tool via RubyGems:

gem install pxdexter

Alternatively, use Homebrew or Docker:

• Homebrew: brew install dexter
• Docker: docker pull ankane/dexter

If you need to see how Dexter is processing queries, use the following flags to increase verbosity:

dexter --log-sql --log-level debug2

Dexter can collect queries from several different sources. You must specify exactly one source:

• --pg-stat-statements: Uses the PostgreSQL pg_stat_statements extension.
• --pg-stat-activity: Uses the PostgreSQL pg_stat_activity view.
• --stdin: Reads queries from standard input.
• File paths: Pass one or more file paths as arguments. Dexter will attempt to infer the --input-format from the file extension (supported formats: csv, json, sql).
• --statement: Process a single specific statement provided via -s or --statement.

If you encounter issues installing HypoPG, check the following:

Specify pg_config path

If you have multiple Postgres installations, set the PG_CONFIG environment variable before running make install:

export PG_CONFIG=/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config

Missing header error

If you see fatal error: postgres.h: No such file or directory, install the Postgres development files.

For Ubuntu/Debian:

sudo apt-get install postgresql-server-dev-18

(Replace 18 with your actual Postgres server version)

Use these flags to refine how Dexter selects queries and manages tables:

• --min-calls <N>: Minimum number of times a query must be called before being considered for indexing.
• --min-time <N>: Minimum total time (in minutes) a query must have run.
• --interval <N>: When streaming logs, the time to wait (in seconds) between processing queries.
• --analyze: Ask Dexter to analyze tables it encounters that haven't been analyzed in the past hour.
• --exclude <table1,table2>: Exclude specific tables from indexing.
• --include <table3,table4>: Only index specific tables.

Dexter supports all standard psql connection options, including URIs and connection strings.

Standard flags:

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

Connection URIs:

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

Connection strings:

host=localhost port=5432 dbname=mydb

Call process_queries(queries) on an instance of Dexter::Indexer to perform the full analysis lifecycle. The method performs the following steps:

1. Resolves tables and filters queries based on include/exclude settings.
2. Optionally runs ANALYZE on relevant tables.
3. Calculates the initial execution plan cost for each query.
4. Identifies candidate columns (excluding json, jsonb, and point types).
5. Creates hypothetical indexes using hypopg to test potential improvements.
6. Determines which indexes provide sufficient cost savings based on min_cost_savings_pct.
7. If create: true was passed during initialization, it will automatically execute the CREATE INDEX commands for the winning suggestions.

The Dexter::Indexer is the core engine used to analyze queries and suggest potential indexes. You initialize it with a database connection and an options hash to control its behavior.

Key configuration options include:

• create: (Boolean) If true, Dexter will automatically create the suggested indexes.
• tablespace: The PostgreSQL tablespace to use when creating indexes.
• exclude: A list of tables to ignore during analysis.
• include: A comma-separated string of tables to focus on.
• analyze: (Boolean) If true, Dexter will run ANALYZE on tables if their statistics are older than one hour.
• min_cost: The minimum initial query cost required for a query to be considered for indexing.
• min_cost_savings_pct: The minimum percentage of cost reduction required to suggest an index.
• log_explain: (Boolean) If true, prints the EXPLAIN output to stdout.

The Dexter::Collector class is responsible for managing the collection of query statistics. When initializing a collector, you must specify thresholds for the minimum execution time and minimum number of calls required for a query to be considered significant.

Note that min_time is provided in minutes and is internally converted to milliseconds.