Explicitly distinguish similar queries in pg_stat_statements?

Question

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?

Answer 1

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.

Answer 2

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 ? = ?

Answer 3

No, there isn't.

If you need that, and the statement has a long duration, you can use log_min_duration_statement and aggregate the durations yourself.