How to link jobs on coordinator and workers on a Citus database on PostgreSQL 12

Question

I have Citus extension on a PostgresSQL server. And I want to see the statistics from pg_stat_statements of each worker through the coordinator node. However, there is no column to match the tables from coordinator and workers. Does anybody know how can I do that?

I am also interested on how the queryId is being computed by PostgreSQL.

So the pg_stat_statements tables on the coordinator would show something like:

userid | dbid | queryid |      query       | other statistics related columns 
1      |  2   | 123     | SELECT * FROM a; | ...

While the pg_stat_statements tables on the worker would show something like:

userid | dbid | queryid |          query          | other statistics related columns 
1      |  2   | 456     | SELECT * FROM a_shard1; | ...
1      |  2   | 789     | SELECT * FROM a_shard2; | ...

Answer 1

You can match the table names on workers (shards) to the distributed tables on the coordinator with the help of pg_dist_partition, and pg_dist_shard_placement tables. For matching the stats, you can check citus_stat_statements view.

Answer 2

(Cannot reply above answer so adding my answer here)
You can use below query to list location of shards of a specific table in a specific worker node (See last three filters in WHERE clause).

SELECT pg_dist_shard.shardid, pg_dist_node.nodename, pg_dist_node.nodeport
FROM pg_dist_shard, pg_dist_placement, pg_dist_node
WHERE pg_dist_placement.groupid = pg_dist_node.groupid AND
      logicalrelid = '<distributedTableName>'::regclass AND
      pg_dist_node.nodename = '<nodeName>' AND
      pg_dist_node.nodeport = '<nodePort>';

Then you can execute below query in worker node of your interest to see what Citus executes for a specific shard in that worker node:

SELECT * FROM pg_stat_statements WHERE query LIKE '%_<shardId>%';