pg_stat_activity – Long Running Easy Queries in PostgreSQL

After application server start its connection pool fills quickly and application can't work properly.
I went through pg_stat_activity and noticed lots of queries running for minutes:

But postgres's slow query log does not show any long-running queries (i've set 100ms as minimum query time).

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2

I suggested that there are some locks on that table but SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED; shows nothing.

Structure of persistence_journal is pretty easy:

CREATE TABLE IF NOT EXISTS public.persistence_journal (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

persistence_journal contains 1 678 121 rows with ~37 000 unique persistence_ids with average 44 rows per persistence_id.

Running SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
shows:

active  1
idle    43
idle in transaction 45

This "transaction" contains two pretty simple queries:


select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2
select max("deleted_to") from "persistence_deleted_to" where "persistence_id" = $1


persistence_deleted_to contains zero rows.

System's top shows almost zero CPU load.

PostgreSQL 9.4, Ubuntu 14.04