# Much improved statement statistics coming to Postgres 9.2

DevFeed: [Much improved statement statistics coming to Postgres 9.2](<https://devfeed.tech/articles/much-improved-statement-statistics-coming-to-postgres-9-2-33642.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2012/03/much-improved-statement-statistics.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2012-03-29T13:02:00Z

Content type: opinion

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Parsing](<https://devfeed.tech/topics/parsing.md>)

Tags: [c](<https://devfeed.tech/tags/c.md>), [database](<https://devfeed.tech/tags/database.md>), [optimisation](<https://devfeed.tech/tags/optimisation.md>), [performance](<https://devfeed.tech/tags/performance.md>), [pg-stat-statements](<https://devfeed.tech/tags/pg-stat-statements.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>)

## AI overview

This article discusses an improvement to PostgreSQL's pg_stat_statements module for Postgres 9.2. The change normalizes similar queries by fingerprinting analyzed parse-tree fields, allowing statistics from queries that differ only in constants to be grouped together for production performance analysis.

## Source excerpt

There is a tendency for people with an interest in improving databases performance to imagine that it mostly boils down to factors outside of their application - the hardware, operating system configuration, and database settings. While these are obviously crucially important, experience suggests that in most cases, by far the largest gains are to be had by optimising the application's interaction with the database. Doing so invariably involves analysing what queries are being executed in production, their costs, and what the significance of the query is to the application or business process that the database supports. PostgreSQL has had a module available in contrib since version 8.4 - pg_stat_statements, originally developed by Takahiro Itagaki. The module blames execution costs on queries, so that bottlenecks in production can be isolated to points in the application. It does so by providing a view that is continually updated, giving real-time statistical information. Here is an example from the Postgres 9.2 docs: bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls | 3000 total_time | 9.60900100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; calls | 3000 total_time | 8.015156 rows | 2990 hit_percent | 99.9731126579631345 -[ RECORD 3 ]--------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_time | 0.310624 rows | 100000 hit_percent | 0.303