pg_qualstats
An extension collecting statistics about quals
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_qualstats | 2.1.3 | STAT | BSD 3-Clause | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 6240 | pg_qualstats | No | Yes | Yes | Yes | No | No | - |
| Related | hypopg pg_stat_kcache powa pg_stat_statements index_advisor pre_prepare pg_show_plans pg_stat_monitor |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 2.1.3 | 1817161514 | pg_qualstats | - |
| RPM | PGDG | 2.1.2 | 1817161514 | pg_qualstats_$v | - |
| DEB | PGDG | 2.1.3 | 1817161514 | postgresql-$v-pg-qualstats | - |
Install
You can install pg_qualstats directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_qualstats; # Install for current active PG version
pig ext install -y pg_qualstats -v 18 # PG 18
pig ext install -y pg_qualstats -v 17 # PG 17
pig ext install -y pg_qualstats -v 16 # PG 16
pig ext install -y pg_qualstats -v 15 # PG 15
pig ext install -y pg_qualstats -v 14 # PG 14
dnf install -y pg_qualstats_18 # PG 18
dnf install -y pg_qualstats_17 # PG 17
dnf install -y pg_qualstats_16 # PG 16
dnf install -y pg_qualstats_15 # PG 15
dnf install -y pg_qualstats_14 # PG 14
apt install -y postgresql-18-pg-qualstats # PG 18
apt install -y postgresql-17-pg-qualstats # PG 17
apt install -y postgresql-16-pg-qualstats # PG 16
apt install -y postgresql-15-pg-qualstats # PG 15
apt install -y postgresql-14-pg-qualstats # PG 14
Preload:
shared_preload_libraries = 'pg_qualstats';
Create Extension:
CREATE EXTENSION pg_qualstats;
Usage
pg_qualstats keeps statistics on predicates found in WHERE clauses and JOIN conditions. It tracks which columns are most frequently queried and which are queried together, enabling index recommendations.
Viewing Predicate Statistics
-- Raw predicate statistics for current database
SELECT * FROM pg_qualstats;
-- Human-readable aggregated form
SELECT * FROM pg_qualstats_pretty;
-- Aggregated per-attribute statistics
SELECT * FROM pg_qualstats_all;
-- Predicates aggregated by query
SELECT * FROM pg_qualstats_by_query;
Index Advisor
Generate index suggestions based on collected predicate statistics:
-- Suggest indexes (filtering predicates with >1000 rows and >30% selectivity)
SELECT v FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'indexes') v;
-- Show predicates that couldn't be optimized
SELECT v FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v;
Utility Functions
-- Get stored query text for a queryid
SELECT pg_qualstats_example_query(queryid);
-- Get all stored query texts
SELECT * FROM pg_qualstats_example_queries();
-- Reset all statistics
SELECT pg_qualstats_reset();
Configuration
| Parameter | Default | Description |
|---|---|---|
pg_qualstats.enabled | true | Enable/disable collection |
pg_qualstats.track_constants | true | Track individual constant values |
pg_qualstats.max | 1000 | Maximum tracked predicates and query texts |
pg_qualstats.resolve_oids | false | Resolve OIDs at query time (uses more space) |
pg_qualstats.track_pg_catalog | false | Track predicates on pg_catalog objects |
pg_qualstats.sample_rate | -1 | Fraction of queries to sample (-1 = auto: 1/max_connections) |
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.