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:

../_images/pg_qualstats.png

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/dalibo/pg_qualstats/archive/1.0.2.tar.gz -O pg_qualstats-1.0.2.tar.gz
tar zxvf pg_qualstats-1.0.2.tar.gz
cd pg_qualstats-1.0.2

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 least pg_stat_statements.max entries if pg_qualstats.track_constants is disabled, else it should be roughly equal to the number of queries executed during powa.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 and WHERE id = 4 will 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.
pg_qualstats.sample_rate:
(Used to be “sample_ratio”) Defaults to 1, which means 1 / 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 to 1/ MAX_CONNECTIONS. For example, a sample_rate of 0.1 would mean one of out ten queries should be sampled.

SQL Objects

The extension defines the following objects:

functionpg_qualstats_reset()

Resets statistics gathered by pg_qualstats.

functionpg_qualstats()
Returns:

A SETOF record containing the data gathered by pg_qualstats

Attributes:
userid (oid):
the user who executed the query
dbid (oid):
the database on which the query was executed
lrelid (oid):
oid of the relation on the left hand side
lattnum (attnum):
attribute number of the column on the left hand side
opno (oid):
oid of the operator used in the expression
rrelid (oid):
oid of the relation on the right hand side
rattnum (attnum):
attribute number of the column on the right hand side
uniquequalnodeid(bigint):
hash of the parent AND expression, if any. This is useful for identifying predicates which are used together.
qualnodeid(bigint):
the predicate hash. Everything (down to constants) is used to compute this hash

occurences (bigint): the number of times this predicate has been seen

execution_count (bigint):
the total number of execution of this predicate.
nbfiltered (bigint):
the number of lines filtered by this predicate
constant_position (int):
the position of the constant in the original query, as filled by the lexer.
queryid (oid):
the queryid identifying this query, as generated by pg_stat_statements
constvalue (varchar):
a string representation of the right-hand side constant, if any, truncated to 80 bytes.
eval_type (char):
the evaluation type. Possible values are f for execution as a filter (ie, after a Scan) or i if it was evaluated as an index predicate. If the qual is evaluated as an index predicate, then the nbfiltered value will most likely be 0, except if there was any rechecked conditions.

Example:

powa=# select * from powa_statements where queryid != 2;
powa=# select * from pg_qualstats();
-[ RECORD 1 ]-----+-----------
userid            | 16384
dbid              | 850774
lrelid            | 851367
lattnum           | 1
opno              | 417
rrelid            |
rattnum           |
qualid            |
uniquequalid      |
qualnodeid        | 1711571257
uniquequalnodeid  | 466568149
occurences | 1
execution_count | 1206 nbfiltered | 0 constant_position | 47 queryid | 3644521490 constvalue | 2::integer eval_type | f
functionpg_qualstats_names()

This function is the same as pg_qualstats, but with additional columns corresponding to the resolved names, if pg_qualstats.resolve_oids is set to true.

Returns:

The same set of columns than pg_qualstats(), plus the following ones:

rolname (text):
the name of the role executing the query. Corresponds to userid.
dbname (text):
the name of the database on which the query was executed. Corresponds to dbid.
lrelname (text):
the name of the relation on the left-hand side of the qual. Corresponds to lrelid.
lattname (text):
the name of the attribute (column) on the left-hand side of the qual. Corresponds to rrelid.
opname (text):
the name of the operator. Corresponds to opno.
viewpg_qualstats

This view is just a simple wrapper on the pg_qualstats() function, filtering on the current database for convenience.

viewpg_qualstats_pretty

This view resolves oid “on the fly”, for the current database.

Returns:
left_schema (name):
the name of the left-hand side relation’s schema.
left_table (name):
the name of the left-hand side relation.
left_column (name):
the name of the left-hand side attribute.
operator (name):
the name of the operator.
right_schema (name):
the name of the right-hand side relation’s schema.
right_table (name):
the name of the right-hand side relation.
right_column (name):
the name of the operator.
execution_count (bigint):
the total number of time this qual was executed.
nbfiltered (bigint):
the total number of tuples filtered by this qual.
typequal

Attributes:

relid (oid):
the relation oid
attnum (integer):
the attribute number
opno (oid):
the operator oid
eval_type (char):
the evaluation type. See pg_qualstats() for an explanation of the eval_type.
typequalname

Pendant of qual, but with names instead of oids

Attributes:

relname (text):
the relation oid
attname (text):
the attribute number
opname (text):
the operator name
eval_type (char):
the evaluation type. See pg_qualstats() for an explanation of the eval_type.