How can I see schema for queries in pg_stat_activity?

Question

I am trying to sort out some app performance issues with slow queries. We are using Postgresql 9.2. I can see the queries in progress easily:

postgres=# select now() - query_start, query from pg_stat_activity where state <> 'idle';

00:00:01.535388 | select bla from product where ...

I need to know what schema the tables listed in the query are in. How can I see which "product" table is being queried given that there are hundreds of them in different schemas in the same database.

Answer 1

pg_stat_activity view calls pg_stat_get_activity(pid int) internal function. You have no chances to change query text in result. There is only one solution for now - call your queries with schema names:

select bla from myschema.product where ...