pg_stat_statements is (thankfully) pretty smart at normalizing queries to aggregate stats regardless of the arguments you pass. However, I’m facing a situation where a particular query is called from many locations across our application and I would like to get separate statistics based on these various locations. Inspired by the Marginalia library, I tried to append SQL comments to annotate the query. Unfortunately it doesn’t work. See this example:
SELECT * FROM users WHERE email = 'a@b.c' /* action: signup */ SELECT * FROM users WHERE email = 'x@y.z' /* action: login */
What happens is that pg_stat_statements stores a normalized representation of the query with the first comment it sees:
SELECT * FROM users WHERE email = $1 /* action: signup */
Then, if I call the same query with different comments (or no comment at all), the stats will be aggregated into the same item. Comments are effectively ignored from the normalized query representation.
Is there any way to call equivalent SQL queries but have them tracked separately by pg_stat_statements?
Advertisement
Answer
I think a possible solution to your problem. It is not the best solution, but maybe it is helpful for you.
You can add more restrictions to the WHERE clause of very similar queries located in different places, like “true = true” to “trick” pg_stat_statements and get separate statistics (At least in Postgresql 9.2 work).
Example:
SELECT * FROM users WHERE email = 'a@b.c' SELECT * FROM users WHERE email = 'x@y.z' AND true = true
pg_stat_statements output (“query” field):
SELECT * FROM users WHERE ? = ? SELECT * FROM users WHERE ? = ? AND ? = ?