How pg_qualstats Helps You Optimize PostgreSQL Queries

Performance tuning in PostgreSQL is often a puzzle. Poorly optimized queries can consume excessive CPU and I/O, slow down applications, and make scaling painful. While PostgreSQL provides tools like EXPLAIN and pg_stat_statements for query analysis, these focus on execution plans and query-level stats.

But what about the conditions inside queries — the WHERE, JOIN, and HAVING clauses that determine how much data gets filtered? That’s where the pg_qualstats extension comes in.

In this post, we’ll explore what pg_qualstats does, how it works, and why its Index Advisor feature makes it a game-changer for performance optimization. We’ll also walk through installation, configuration, its key views, and real-world output from the Index Advisor.

What is pg_qualstats?

pg_qualstats is an open-source PostgreSQL extension that tracks and analyzes qualifiers — the predicates used to filter rows in queries. Unlike pg_stat_statements, which aggregates entire query statistics, pg_qualstats focuses on expressions inside queries.

For example:

SELECT * FROM orders WHERE customer_id = 42;

Instead of storing the literal 42, pg_qualstats records this as customer_id = ?. By aggregating similar predicates across queries and sessions, it helps identify patterns and shows which filters might benefit from indexes.

This visibility makes it easier to tune queries, reduce sequential scans, and create indexes where they matter most.

Installation and Setup

From source:

git clone https://github.com/powa-team/pg_qualstatscd pg_qualstatsmakesudo make install

Enable it in PostgreSQL

Add the extension:

CREATE EXTENSION pg_qualstats;

Update postgresql.conf:

shared_preload_libraries = 'pg_qualstats'pg_qualstats.track_utility = off         # Ignore VACUUM/DDLpg_qualstats.max = 10000                 # Limit tracked entriespg_qualstats.queryid_threshold = 100     # Only track queries run >100 times

Restart PostgreSQL, and it begins tracking.

To reset stats:

SELECT pg_qualstats_reset();

Key Features of pg_qualstats

• Predicate Monitoring – Captures filters from WHERE, JOIN, and subqueries.
• Execution Statistics – Tracks how often conditions run and how selective they are.
• Query Normalization – Groups similar queries to avoid duplication.
• Index Advisor – Suggests indexes based on predicate usage patterns.

The Index Advisor of pg_qualstats

Indexes are essential for query performance, but creating too many slows down writes and bloats storage. The Index Advisor in pg_qualstats provides data-driven recommendations by analyzing real predicate usage.

SELECT * FROM pg_qualstats_index_advisor();

Example output:

{ "indexes" : [   {"ddl" : "CREATE INDEX ON public.account_asset USING btree (frequency_date)",     "queryids" : [-3140656395024112640]},   {"ddl" : "CREATE INDEX ON public.ir_model USING btree (res_field     "queryids" : [-6116874720114895025]},   {"ddl" : "CREATE INDEX ON public.ir_model_access USING btree (group_id)",     "queryids" : [1544699447223701095]} ], "unoptimised" : [   {"qual" : "ir_attachment.url ~~ ?",     "queryids" : [-9184376422200194705,-2624294692995292147,2176812084577047332]},   {"qual" : "ir_module_module.state ~~ ?",     "queryids" : [4425032441216716078]} ]}

• Recommended indexes (CREATE INDEX ...) on frequently filtered columns.
• A list of unoptimized conditions (like url ~~ ? using pattern matching), where a normal index may not help, suggesting the need for trigram indexes (pg_trgm) or other approaches.

This transforms guesswork into actionable tuning steps.

Exploring the Four pg_qualstats Views

Alongside the Index Advisor, pg_qualstats provides several views to analyze predicate behavior.

1. pg_qualstats (Raw Details per Predicate)

SELECT * FROM pg_qualstats LIMIT 1;

Example output:

-[ RECORD 1 ]-------------+--------------------userid                    | 16384dbid                      | 1906360lrelid                    | 1909721lattnum                   | 1opno                      | 96rrelid                    | rattnum                   | qualid                    | uniquequalid              | qualnodeid                | 2065864505uniquequalnodeid          | 1317883167occurences                | 1execution_count           | 43nbfiltered                | 42min_err_estimate_ratio    | 0max_err_estimate_ratio    | 0mean_err_estimate_ratio   | 0stddev_err_estimate_ratio | 0min_err_estimate_num      | 0max_err_estimate_num      | 0mean_err_estimate_num     | 0stddev_err_estimate_num   | 0constant_position         | 150queryid                   | 9057374734536084296constvalue                | 97::integereval_type                 | f

This is the lowest-level view that records every qualifier used in queries. It includes user IDs, relation OIDs, attribute numbers, operators, constant values, and selectivity metrics.

Use this view when you need fine-grained insights into which columns and operators are being filtered, and how often. It’s particularly useful for tracing problematic filters at the system level.

2. pg_qualstats_all (Aggregated by Column & Operator)

SELECT * FROM pg_qualstats_all LIMIT 1;

Example output:

-[ RECORD 1 ]---+---------------------dbid            | 1906360relid           | 1249userid          | 10queryid         | -5008944115988292656attnums         | {18}opno            | 91qualid          | 933878808occurences      | 2execution_count | 52nbfiltered      | 0qualnodeid      | 933878808

Instead of listing each predicate separately, this view aggregates statistics across columns and operators. It summarizes execution counts and filter frequencies for each column/operator pair.

This makes it easier to identify high-impact columns that are repeatedly filtered, which is valuable when deciding where indexes could bring the most benefit

3. pg_qualstats_pretty (Readable Format)

SELECT * FROM pg_qualstats_pretty LIMIT 1;

Example output:

-[ RECORD 1 ]---+-------------left_schema     | pg_catalogleft_table      | pg_attributeleft_column     | attisdroppedoperator        | pg_catalog.=right_schema    | right_table     | right_column    | occurences      | 2execution_count | 52nbfiltered      | 0

This view transforms the raw data into a readable format, showing schema, table, column, and operator names directly. It hides the low-level OIDs and technical identifiers, making the results accessible to developers and DBAs who prefer plain SQL output.

Ideal for quick reviews during performance tuning sessions, when you want to see which filters are actually being applied without digging through system OIDs.

4. pg_qualstats_by_query (Predicate Usage by Query)

SELECT * FROM pg_qualstats_by_query LIMIT 1;

Example output:

-[ RECORD 1 ]----+---------------------uniquequalnodeid | 122565045dbid             | 1906360userid           | 16384qualnodeid       | 1695674558occurences       | 1execution_count  | 1nbfiltered       | 0queryid          | 3616311912644832466constvalues      | {827::integer}quals            | {"(1909728,1,96,i)"}

This view links predicates back to the specific queries that triggered them. It shows query IDs, constants, and qualifier usage, so you can connect filter conditions to real workloads.

Perfect for prioritizing query-level optimization, because it highlights which queries repeatedly rely on expensive or poorly indexed conditions.

When to Use pg_qualstats

Use it when:

• Your workload has repetitive queries with WHERE filters.
• You’re unsure which columns should be indexed.
• You want historical predicate insights.
• You’re troubleshooting slow queries stuck on sequential scans.

Keep in mind:

• It doesn’t track actual index usage (pg_stat_user_indexes does).
• Large workloads may generate lots of stats if pg_qualstats.max is too high.

Conclusion

Query optimization in PostgreSQL is both art and science. While PostgreSQL already provides strong tools, pg_qualstats adds a missing dimension: visibility into the predicates that shape query performance.

With its Index Advisor, it goes further — generating real index recommendations and highlighting unoptimized conditions. And with its four views (pg_qualstats, pg_qualstats_all, pg_qualstats_pretty, pg_qualstats_by_query), you can analyze predicate behavior from multiple angles.

If performance matters to you (and it always does), pg_qualstats should be part of your toolkit. Install it, let it collect query stats, and use its advisor to build the indexes that matter most.