Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.

On 27th of March, Andrew Dunstan committed patch:

Add stats for min, max, mean, stddev times to pg_stat_statements.
 
The new fields are min_time, max_time, mean_time and stddev_time.
 
Based on an original patch from Mitsumasa KONDO, modified by me. Reviewed by Petr Jelínek.

Continue reading Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.

What logging has least overhead?

When working with PostgreSQL you generally want to get information about slow queries. The usual approach is to set log_min_duration_statement to some low(ish) value, run your app, and then analyze logs.

But you can log to many places – flat file, flat file on another disk, local syslog, remote syslog. And – perhaps, instead of log_min_duration_statement – just use pg_stat_statements?

Well, I wondered about it, and decided to test.

Continue reading What logging has least overhead?

Waiting for 9.2 – pg_stat_statements improvements

Three interesting patches:

  • On 27th of March, Robert Haas committed patch:
    New GUC, track_iotiming, to track I/O timings.
     
    Currently, the only way to see the numbers this gathers is via
    EXPLAIN (ANALYZE, BUFFERS), but the plan is to add visibility through
    the stats collector and pg_stat_statements in subsequent patches.
     
    Ants Aasma, reviewed by Greg Smith, with some further changes by me.
  • On 27th of March, Robert Haas committed patch:
    Expose track_iotiming information via pg_stat_statements.
     
    Ants Aasma, reviewed by Greg Smith, with very minor tweaks by me.
  • On 29th of March, Tom Lane committed patch:

    Improve contrib/pg_stat_statements to lump "similar" queries together.
     
    pg_stat_statements now hashes selected fields of the analyzed parse tree
    to assign a "fingerprint" to each query, and groups all queries with the
    same fingerprint into a single entry in the pg_stat_statements view.
    In practice it is expected that queries with the same fingerprint will be
    equivalent except for values of literal constants.  To make the display
    more useful, such constants are replaced by "?" in the displayed query
    strings.
     
    This mechanism currently supports only optimizable queries (SELECT,
    INSERT, UPDATE, DELETE).  Utility commands are still matched on the
    basis of their literal query strings.
     
    There remain some open questions about how to deal with utility statements
    that contain optimizable queries (such as EXPLAIN and SELECT INTO) and how
    to deal with expiring speculative hashtable entries that are made to save
    the normalized form of a query string.  However, fixing these issues should
    require only localized changes, and since there are other open patches
    involving contrib/pg_stat_statements, it seems best to go ahead and commit
    what we've got.
     
    Peter Geoghegan, reviewed by Daniel Farina

Continue reading Waiting for 9.2 – pg_stat_statements improvements

Waiting for 8.4 – pg_stat_statements

On 4th of January, Tom Lane committed patch by Takahiro Itagaki, which adds new contrib module – pg_stat_statements:

Log Message:
-----------
Add contrib/pg_stat_statements for server-wide tracking of statement execution
statistics.
 
Takahiro Itagaki

Continue reading Waiting for 8.4 – pg_stat_statements