pg_qualstats is a PostgreSQL extension keeping statistics on predicates found
`WHERE` statements and
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.
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:
Then install the compiled package:
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:
The following configuration parameters are available, in postgresql.conf:
true. Enable pg_qualstats. Can be useful if you want to enable / disable it without restarting the server.
1000. Number of entries to keep. As a rule of thumb, you should keep at least
pg_qualstats.track_constantsis disabled, else it should be roughly equal to the number of queries executed during
powa.frequencyinterval of time.
false. Determine if predicates on pg_catalog tables should be tracked too.
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.
true. If true, each new value for each predicate will result in a new entry. Eg,
WHERE id = 3and
WHERE id = 4will results in two entries in pg_qualstats. If disabled, only one entry for
WHERE 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.
(Used to be “sample_ratio”) Defaults to
-1, which means
1 / MAX_CONNECTIONSThe ratio of queries that should be sampled. 1 means sample every single query, 0 basically deactivates the feature, and -1 is automatically sized to
1/ MAX_CONNECTIONS. For example, a sample_rate of
0.1would mean one of out ten queries should be sampled.
The extension defines the following objects: