Why does pg_stat_activity show a query on an idle session?

Because that column is documented as the backend’s most recent statement, not its current one. When the state is active it is what is running; in every other state it is what ran last and already finished. A row showing a heavy report and a state of idle is a session doing nothing whatsoever, and reading it as a running query is how an incident call spends twenty minutes on a statement that completed before anybody logged in.

Reading the row correctly

The state column decides everything, so read it first and the text second. Then pick the right clock: the query start time belongs to that most recent statement, while the state change time tells you how long the session has been in its current state. For an idle session only the second one means anything.

One state deserves separate treatment. A session idle inside a transaction is doing nothing and holding everything, including locks and the cutoff that stops vacuum cleaning up. It looks as harmless as an ordinary idle session in a list and is not, which is why idle in transaction is its own term and how to stop those sessions is its own question.

There is a second trap in the same column. The text is truncated at a fixed size by default, so two long statements that differ only near the end are indistinguishable, and a dashboard that groups by that text will happily merge them.

Sampling beats staring

One look at the view is one sample of a system that changes hundreds of times a second. The picture that is worth having comes from sampling it on a short interval and counting states, which turns a snapshot into a distribution and makes a pattern visible that no single look would show. Active session history covers building that.

For the sessions that are genuinely active, the field worth counting is not the query text but what each one is waiting on, because that is what separates a server that is short of storage from one that is short of processor from one whose sessions are queueing on each other. Wait event types covers the categories and what each implies.

And treat the whole view as a view of sessions, not of work. A statement that has been running for an hour appears here and nowhere else; a statement that ran ten thousand times in that hour appears here almost never, which is why the statement statistics do not show everything either.