pg_qualstats¶
pg_qualstats is a PostgreSQL extension keeping statistics on predicates found
in `WHERE`
statements and `JOIN`
clauses.
The goal of this extension is to allow the DBA to answer some specific questions, whose answers are quite hard to come by:
- what is the set of queries using this column ?
- what are the values this where clause is most often using ?
- do I have some significant skew in the distribution of the number of returned rows if use some value instead of one another ?
- which columns are often used together in a WHERE clause ?
Where is it used in powa-web ?¶
If the extension is available, you should see a “list of quals” table on the query page, as well as explain plans for your query and a list of index suggestions:

From this list, you can then go on to the per-qual page.
Installation¶
As seen in Quickstart, the PostgreSQL development packages should be available.
First, download and extract the latest release of pg_qualstats:
wget https://github.com/powa-team/pg_qualstats/archive/2.0.4.tar.gz -O pg_qualstats-2.0.4.tar.gz tar zxvf pg_qualstats-2.0.4.tar.gz cd pg_qualstats-2.0.4
Then, compile the extension:
make
Then install the compiled package:
make install
Then you just have to declare the extension in the postgresql.conf
file, like this :
shared_preload_libraries = 'pg_stat_statements,pg_qualstats'
Restart the PostgreSQL server to reload the libraries.
Connect to the server as a superuser and type:
CREATE EXTENSION pg_qualstats;
Using with PoWA¶
If you want PoWA to handle this extension, you have to connect as a superuser on the database where you installed PoWA, and type:
SELECT powa_qualstats_register();
Configuration¶
The following configuration parameters are available, in postgresql.conf:
- pg_qualstats.enabled:
- Defaults to
true
. Enable pg_qualstats. Can be useful if you want to enable / disable it without restarting the server. - pg_qualstats.max:
- Defaults to
1000
. Number of entries to keep. As a rule of thumb, you should keep at leastpg_stat_statements.max
entries ifpg_qualstats.track_constants
is disabled, else it should be roughly equal to the number of queries executed duringpowa.frequency
interval of time. - pg_qualstats.track_pg_catalog:
- Defaults to
false
. Determine if predicates on pg_catalog tables should be tracked too. - pg_qualstats.resolve_oids:
- Defaults to
false
. Determine if during predicates collection, the actual name of the objects should be stored alongside their OIDs. The overhead is quite non-negligible, since each entry will occupy 616 bytes instead of 168. - pg_qualstats.track_constants:
- Defaults to
true
. If true, each new value for each predicate will result in a new entry. Eg,WHERE id = 3
andWHERE id = 4
will results in two entries in pg_qualstats. If disabled, only one entry forWHERE id = ?
will be kept. Turning this off drastically reduces the number of entries to keep, at the price of not getting any hindsight on most frequently used values. - pg_qualstats.sample_rate:
- (Used to be “sample_ratio”)
Defaults to
-1
, which means1 / MAX_CONNECTIONS
The ratio of queries that should be sampled. 1 means sample every single query, 0 basically deactivates the feature, and -1 is automatically sized to1/ MAX_CONNECTIONS
. For example, a sample_rate of0.1
would mean one of out ten queries should be sampled.
SQL Objects¶
The extension defines the following objects: