PgHero Documentation

PgHero uses a multi-step heuristic process to suggest indexes for slow queries. The process follows these steps:

1. Identify slow queries: It retrieves the most time-consuming queries from the pg_stat_statements extension.
2. Parse queries: It uses pg_query to analyze the query structure. It specifically looks for queries targeting a single table that contain a WHERE clause using only =, IN, IS NULL, or IS NOT NULL operators, and/or an ORDER BY clause.
3. Analyze column statistics: It uses the pg_stats view to retrieve estimates regarding the number of distinct rows and the percentage of NULL values for each column involved.
4. Determine index order: For columns in the WHERE clause, it sorts them by highest cardinality (most unique values) to allow the database to narrow the search most efficiently. It performs row estimation as columns are added to the proposed index.
5. Incorporate sorting: It continues the process by adding columns from the ORDER BY clause.
6. Apply stopping criteria: To avoid adding redundant columns, the process stops once the row estimation narrows the result set down to approximately 50 rows, provided the last added columns still provide value.

To track query performance over time, you must create a specific table and schedule a capture task.

1. Create the table in your database (or a separate database):

CREATE TABLE "pghero_query_stats" (
  "id" bigserial primary key,
  "database" text,
  "user" text,
  "query" text,
  "query_hash" bigint,
  "total_time" float,
  "calls" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");

2. Capture stats: Run the following command every 5 minutes. If using a different database for stats, set the PGHERO_STATS_DATABASE_URL environment variable.

docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:capture_query_stats

3. Cleanup: To prevent the table from growing too large, run the clean task with a KEEP_DAYS parameter:

docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:clean_query_stats KEEP_DAYS=14

To avoid running PgHero as a superuser, you can use SECURITY DEFINER functions within a dedicated schema to grant non-superusers access to restricted Postgres functions.

Follow these steps:

1. As a superuser: Create a pghero schema and define SECURITY DEFINER functions for viewing queries, killing queries, accessing query stats, resetting stats, and viewing suggested indexes.
2. As a superuser: Create a dedicated pghero user and grant it access to the pghero schema and necessary public sequences.
3. As a superuser: Set the search_path and lock_timeout for the pghero user to ensure it looks in the correct schema and fails quickly on locks.
4. As the migrations user: Ensure future sequences in the public schema are accessible to the pghero user using ALTER DEFAULT PRIVILEGES.

-- 1. Run as superuser to setup schema and security definer functions
CREATE SCHEMA pghero;

-- view queries
CREATE OR REPLACE FUNCTION pghero.pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$
  SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stat_activity AS SELECT * FROM pghero.pg_stat_activity();

-- kill queries
CREATE OR REPLACE FUNCTION pghero.pg_terminate_backend(pid int) RETURNS boolean AS
$$
  SELECT * FROM pg_catalog.pg_terminate_backend(pid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- query stats
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stat_statements AS SELECT * FROM pghero.pg_stat_statements();

-- query stats reset
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) RETURNS void AS
$$
  SELECT public.pg_stat_statements_reset(userid, dbid, queryid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- suggested indexes
CREATE OR REPLACE FUNCTION pghero.pg_stats() RETURNS
TABLE(schemaname name, tablename name, attname name, null_frac real, avg_width integer, n_distinct real) AS
$$
  SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_catalog.pg_stats;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pghero.pg_stats AS SELECT * FROM pghero.pg_stats();

-- 2. Create the pghero user
CREATE ROLE pghero WITH LOGIN ENCRYPTED PASSWORD 'secret';
GRANT CONNECT ON DATABASE <dbname> TO pghero;
ALTER ROLE pghero SET search_path = pghero, pg_catalog, public;
ALTER ROLE pghero SET lock_timeout = '1s';
GRANT USAGE ON SCHEMA pghero TO pghero;
GRANT SELECT ON ALL TABLES IN SCHEMA pghero TO pghero;

-- 3. Grant permissions for current sequences
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pghero;

-- 4. Grant permissions for future sequences (Run as <migrations-user>)
ALTER DEFAULT PRIVILEGES FOR ROLE <migrations-user> IN SCHEMA public GRANT SELECT ON SEQUENCES TO pghero;