Peek into Query Hash (query_id) in PostgreSQL

PostgreSQL has an extension called pg_stat_statements to track top SQLs and it has queryid and query columns since ever (at least since I was introduced to PG few years back 😬). However pg_stat_activity got this query_id column from PostgreSQL v14 onwards.

pg_stat_statements do not capture running SQLs and SQLs are normalized (remove constant and hard coded values) before storing. This means SELECT pg_sleep(10); and SELECT pg_sleep(20); will be having a single entry as SELECT pg_sleep($1);.

Have you ever seen pg_stat_statements having two entries for the same query, userid, dbid but different queryid?

select userid,dbid,queryid, query from pg_stat_statements;

 userid | dbid  |       queryid       |                   query
--------+-------+---------------------+------------------------------------
  16388 | 14366 | 7404201745246207491 | select * from test where age=$1
  16388 | 14366 | 6006622278401625462 | select * from test where age=$1

This is very well possible because of two tables with same name defined in seperate schemas and a userid is calling the same query on these two different tables by changing the search_path (to avoid adding fully schema qualified names).

But there is another scenario when we can see multiple entries for the same SQL in pg_stat_statements.

PostgreSQL does not generate query_id in the pg_stat_activity until the SQL is parsed. Let’s understand with an example.

/* sesion 1 :: Take an exclusive lock on the table */

postgres=> BEGIN;
BEGIN
postgres=*> alter table test add column n2 int;
ALTER TABLE

/* sesion 2 :: Query is blocked due to exclusive lock taken by session 1 */

postgres=> select * from test;

<<waiting on session1>>

/* session 3 :: query_id column is blank */

select query_id,query from pg_stat_activity where query like '%test%';
       query_id       |                                    query
----------------------+-----------------------------------------------------
                      | select * from test;

Why is query_id not generated until parsing?

One reason would be that the blocking session can possibly change the structure of the table and hence a waiting query could have an entirely different meaning after the blocker finishes execution.

Here session 1 added a column in the table and when it finishes, session 2 proceeds but it generates a new query_id because now SELECT * means returning an additional column n1 and hence new query_id is generated and a separate entry is added in the pg_stat_statements.

This all makes sense. But deferring the query_id generation causes one problem. Say you have an observability tool to show up the top wait events against query_id (and then those are mapped to respective query) but the problem in such cases is that the lock wait events are getting generated as query is blocked/waiting but you are not able to map those wait events against a query_id because it’s not generated yet.

Out of curiosity, I was checking Performance Insights for RDS PostgreSQL and how it maps the waits events. What I observed is that it uses the old query_id when query is waiting on parsing but when it proceeds for the execution, it shows any wait events on the new query_id. That means a single query execution is split across two query_id, waits before parsing gets mapped to old query_id and waits during execution gets mapped to new query_id.