Skip to content


Subversion checkout URL

You can clone with
Download ZIP
provides a simple query execution time histogram for PostgreSQL
Tree: 790b35605d

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.


Query Histogram Extension
This is a PostgreSQL extension providing a simple histogram of queries
(according to their duration). Once you install the connection, you
may use three functions and four GUC to control the histogram - get
data, reset it, change the bin size, sampling etc.

The histogram data are stored in a shared memory segment (so that all
backends may share it and it's not lost in case of on disconnections).
The segment is quite small (about 8kB of data) and it's protected by
a System V semaphore. That might cause some performance problems - to
minimize this issue, you may sample only some of the queries (see the
sample_pct GUC option).

Most of the code that interacts directly with the executor comes from
the auto_explain extension (hooks, etc).

Installing the extension is quite simple, especially if you're on 9.1.
In that case all you need to do is this:

   $ make install

and the (after connecting to the database)

   db=# CREATE EXTENSION query_histogram;

If you're on pre-9.1 version, you'll have to do the second part manually
by running the SQL script (query_histogram--x.y.sql) in the database. If
needed, replace MODULE_PATHNAME by $libdir.

There are three functions that you can use to work with the histogram.

  1) query_histogram()        - get data
  2) query_histogram_reset()  - reset data, start collecting again
  3) uery_histogram_refresh() - get sample rate (set by other backend)

The first one is the most important one, as it allows you to read the
current histogram data - just use it as a table:

   db=# SELECT * FROM query_histogram();

The columns of the result are rather obvious:

  a) bin_from, bin_to - bin range (from, to) in miliseconds

  b) bin_count - number of queries in the bin

  c) bin_count_pct - number of queries proportionaly to the total number
                     in the histogram
  d) bin_time - time accumulated by queries in the bin

  e) bin_time_pct - time accumulated by queries in the bin proportionaly
                    to the total time (accumulted by all queries)

The second function may be handy if you need to reset the histogram and
start collecting again (for example you may collect the stats regularly
and reset it).

The last function is related to the shared memory segment - all important
parameters (number of bins, width and sample rate) are stored at the
beginning of the shared segment. As all access to the segment has to be
guarded by a lock, there is a local copy of the sample_pct and it's
refreshed only when the segment is locked for some other reason, e.g.
whenever a query is actually added to the histogram or when you reconnect.
Or you may force it by calling query_histogram_refresh().

But if you don't change the sample_pct (which is probably the case), or
if you don't mind that the other backends are using a stale value for a
while, you don't need to care about this function.

There are three things you may configure on the histogram - number of
bins, width of the bins and sample rate. There are three GUC for this.

  query_histogram.bin_count - number of bins (0-1000), 0 means the
                              histogram is disabled

  query_histogram.bin_width - width of each bin (in miliseconds)

  query_histogram.sample_pct - sampling rate, i.e. what portion of
                               queries will actually be stored in the
                               histogram, e.g. 5 means 5%
So if you want a histogram with 100 bins, each bin 1 second wide, you
may do this

  db=# SET query_histogram.bin_count = 100; /* 100 bins */
  db=# SET query_histogram.bin_width = 1000; /* each 1000 ms wide */

And if you want to sample just 1% of the queries, you may do this

  db=# SET query_histogram.sample_pct = 1; /* sample just 1% */

That's all.
Something went wrong with that request. Please try again.