pg_qualstats

What pg_qualstats does

pg_qualstats hooks into the PostgreSQL query executor and intercepts predicates as they run. For each predicate it encounters — whether in a WHERE clause, a JOIN condition, or a HAVING clause — it records the table, column, operator, and execution statistics. The result is a set of views that show exactly which columns your workload filters on and how heavily.

Unlike pg_stat_statements, which tracks statistics per query, pg_qualstats tracks statistics per predicate. A single query with three WHERE conditions produces three predicate entries. This granularity is what makes it useful for index analysis — you see individual column access patterns rather than whole-query aggregates.

The extension also identifies correlated columns: columns that frequently appear together in predicates on the same query. This is valuable for composite index design, where column order and combination matter.

When to use pg_qualstats

Install it when you want evidence instead of intuition. "Which indexes should I create?" is a question that deserves a proper answer, not a guess derived from slow query logs and a prayer.

• Detecting missing indexes — the pg_qualstats_indexes view shows predicates on columns that lack a matching index
• Prioritizing index creation — sort by execution_count to find the predicates that run most often and filter the most rows
• Designing composite indexes — identify which columns are queried together to determine the best multi-column indexes
• Automated index recommendations — use pg_qualstats_index_advisor() for global index suggestions that consider all predicates simultaneously
• Validating index usage after changes — reset statistics, deploy a change, and see whether predicate patterns shifted

Installation and setup

pg_qualstats is a third-party extension maintained by the PoWA team. It must be added to shared_preload_libraries, which requires a PostgreSQL restart. It is compatible with PostgreSQL 9.4 and later.

-- 1. Add to postgresql.conf (requires restart)
shared_preload_libraries = 'pg_qualstats'

-- 2. Restart PostgreSQL, then create the extension
CREATE EXTENSION pg_qualstats;

-- 3. Verify it's working
SELECT count(*) FROM pg_qualstats;

After the restart, the extension begins sampling queries immediately. Three steps, one restart, and your database starts keeping notes on its own shortcomings. The default sample rate is 1 / max_connections, which provides representative data with minimal overhead. For smaller workloads or thorough analysis, you can set pg_qualstats.sample_rate = 1.0 to capture every query.

Configuration

All parameters are set in postgresql.conf and require a restart to take effect.

-- Key configuration parameters (set in postgresql.conf)
-- pg_qualstats.enabled = true          -- enable/disable collection
-- pg_qualstats.track_constants = true  -- track each constant value individually
-- pg_qualstats.max = 1000             -- max number of predicates to track
-- pg_qualstats.sample_rate = -1        -- fraction of queries to sample
--                                      -- (-1 = automatic: 1/max_connections)
-- pg_qualstats.track_pg_catalog = false -- include system catalog predicates
-- pg_qualstats.resolve_oids = false    -- resolve OIDs at query time
--                                      -- (easier analysis, more memory)

The most commonly adjusted parameter is pg_qualstats.sample_rate. The default automatic rate (-1) works well for most workloads. Set it to 1.0 for complete coverage on low-traffic databases, or to a lower fraction like 0.1 on very high-throughput systems.

If you enable pg_qualstats.resolve_oids, the extension resolves table and column names at query time rather than storing raw OIDs. This makes the data easier to query directly but increases memory usage per entry from 168 bytes to 616 bytes.

Practical queries

Most frequently executed predicates

The pg_qualstats_pretty view joins the raw statistics with catalog information to show human-readable table and column names. This query reveals which predicates run most often — high-frequency predicates on unindexed columns are prime candidates for new indexes.

-- Most frequently executed predicates
SELECT
  left_schema,
  left_table,
  left_column,
  operator,
  execution_count,
  nbfiltered
FROM pg_qualstats_pretty
ORDER BY execution_count DESC
LIMIT 20;

Columns missing indexes

The pg_qualstats_indexes view specifically identifies predicates where no matching index exists. This is the most direct path to finding missing indexes — the extension has done the detective work and is now presenting its findings.

-- Columns used in predicates that have no matching index
SELECT
  relid::regclass AS table_name,
  attnames,
  possible_types,
  execution_count
FROM pg_qualstats_indexes
ORDER BY execution_count DESC;

Global index recommendations

The pg_qualstats_index_advisor() function (available in pg_qualstats 2.0+) performs a global analysis of all tracked predicates and returns a minimal set of CREATE INDEX statements. It considers multi-column combinations and avoids redundant suggestions.

-- Get global index recommendations (pg_qualstats 2.0+)
-- Returns JSON with two keys: "indexes" and "unoptimised"
SELECT pg_qualstats_index_advisor(
  min_filter     := 1000,   -- minimum rows filtered (default)
  min_selectivity := 30     -- minimum selectivity % (default)
);

-- Example output:
-- {
--   "indexes": [
--     "CREATE INDEX ON public.orders USING btree (customer_id)",
--     "CREATE INDEX ON public.events USING btree (created_at, type)"
--   ],
--   "unoptimised": [
--     "orders.metadata ~~* ?"
--   ]
-- }

Resetting statistics

Clear accumulated data to start a fresh collection period. Useful after creating indexes or deploying schema changes — a clean slate, as it were.

-- Reset collected statistics
SELECT pg_qualstats_reset();

Cloud availability

pg_qualstats is a third-party extension that requires shared_preload_libraries access. Most managed PostgreSQL providers do not support it because they restrict which libraries can be loaded at startup.

A conspicuous absence across every major provider. To use pg_qualstats, you need a self-managed PostgreSQL instance where you control postgresql.conf. This includes bare-metal servers, VMs, Docker containers, and managed providers that offer full configuration access (such as Crunchy Bridge or Aiven).

How Gold Lapel relates

Allow me a moment of candour. pg_qualstats is doing, manually and admirably, the first step of what Gold Lapel does continuously. It watches your predicates and tells you where the indexes should be. That insight — that predicate statistics are the key to index optimization — is exactly right. I have no quarrel with the diagnosis.

What happens after the diagnosis is where the work begins. You receive a list of CREATE INDEX statements. Now you must evaluate each one against your write workload. Test it with realistic data. Create it concurrently so production does not lock. Monitor whether it actually gets used. Revisit it in three months when your query patterns have shifted. This is not a criticism of pg_qualstats — it is the reality of what index management demands.

Gold Lapel automates that entire pipeline. It observes query predicates at the proxy level, evaluates index candidates against the full workload — reads and writes — creates indexes concurrently in the background, and monitors their impact over time. Indexes that stop earning their keep are flagged for removal. The same insight pg_qualstats surfaces once, Gold Lapel acts on continuously.

If you are running a self-managed instance and want to understand your predicate landscape before committing to anything, pg_qualstats is a fine place to start. I would simply observe that starting is the easy part.