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?
I finally found an elegant and versatile solution. You can label your queries by prepending a dummy Common Table Expression (WITH clause):
WITH signup AS (SELECT) SELECT * FROM users WHERE email = 'a@b.c'
WITH login AS (SELECT) SELECT * FROM users WHERE email = 'x@y.z'
These will be tracked separately with their name preserved in pg_stat_statements:
WITH signup AS (SELECT) SELECT * FROM users WHERE email = $1
WITH login AS (SELECT) SELECT * FROM users WHERE email = $1
Another advantage over @nachospiu’s answer is that it works with INSERT, UPDATE, DELETE as well as SELECT queries that don’t have a WHERE you can add dummy conditions to.
The downside is that it may not be easy to implement using ORMs.