Thread: pg_stat_statements query normalization
Hello,
Reading code documentation of pg_stat_statements it says
* As of Postgres 9.2, this module normalizes query entries. Normalization
* is a process whereby similar queries, typically differing only in their
* constants (though the exact rules are somewhat more subtle than that) are
* recognized as equivalent, and are tracked as a single entry. This is
* particularly useful for non-prepared queries.
Consider query
SELECT * FROM pgbench_branches LEFT JOIN pgbench_tellers ON pgbench_tellers.bid= pgbench_branches.bid WHERE pgbench_branches.bID=5
Does this mean that all queries with just the constant changing are normalized
pgbench_branches.bID=10,pgbench_branches.bID=15
Or are queries where conditions changed included as well?
pgbench_branches.bID <10,pgbench_branches.bID>15
regards
Sameer
On Sun, Jun 16, 2013 at 11:58 PM, Sameer Thakur <samthakur74@gmail.com> wrote: > Consider query > SELECT * FROM pgbench_branches LEFT JOIN pgbench_tellers ON > pgbench_tellers.bid= pgbench_branches.bid WHERE pgbench_branches.bID=5 > > Does this mean that all queries with just the constant changing are > normalized > > pgbench_branches.bID=10,pgbench_branches.bID=15 > > Or are queries where conditions changed included as well? Why don't you play around with it and see for yourself? In general, queries differing only in the values of constants are considered equivalent by the fingerprinting. pg_stat_statements usefully ignores differences in whitespace and equivalent syntaxes, by virtue of the fact that ultimately the post-parse analysis tree is fingerprinted. You might say that pg_stat_statements leverages the normalization capabilities of the core system by working off this later representation (essentially, the internal representation that the rewriter stage processes). -- Regards, Peter Geoghegan
>Why don't you play around with it and see for yourself?
I did that. Populated a sample table and then queried on it multiple times for each condition (=,>,<) with different constant values. Then queried pg_stat_statements view. Saw three different records corresponding to each condition query text (=?,>?,<?).
Thank you
Sameer