On Sat, Jan 17, 2026 at 02:58:01PM +0100, Lillian Berry wrote: > Logs on our PostgreSQL cluster show frequent slow reads on a simple query by > unique index. > > This issue has been present for some years, and survived multiple server > moves as well as PostgreSQL version upgrades. > Vacuuming or reindexing the table does not seem to help. > > Jan 17 13:38:13 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[172632]: pluralkit-db-dataLOG: > duration: 2322.842 ms execute <unnamed>: SELECT * FROM "members" WHERE "hid" > = $1 > Jan 17 13:38:15 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[157893]: pluralkit-db-dataLOG: > duration: 2155.033 ms execute _auto8: SELECT * FROM "members" WHERE "hid" = > $1 > Jan 17 13:38:16 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[157826]: pluralkit-db-dataLOG: > duration: 2164.851 ms execute _auto10: SELECT * FROM "members" WHERE "hid" = > $1 > Jan 17 13:38:19 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[171076]: pluralkit-db-dataLOG: > duration: 2039.040 ms execute _auto3: SELECT * FROM "members" WHERE "hid" = > $1 > Jan 17 13:38:20 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[162682]: pluralkit-db-dataLOG: > duration: 2189.700 ms execute _auto8: SELECT * FROM "members" WHERE "hid" = > $1 > Jan 17 13:38:22 database-node1-bd9aa148 > pzqzs8zyvqrq1rq25z4drws8dhnbxa59-run-postgres[159824]: pluralkit-db-dataLOG: > duration: 2137.666 ms execute _auto10: SELECT * FROM "members" WHERE "hid" = > $1 > > Parameter detail has been redacted for privacy. > > This is not reproducible on manual queries, whether using the same values or > other random ones. >
Hi, You may want to try auto_explain to try and catch the explain for one of these queries. You should also consider things like a checkpoint or other query that needs a lot of I/O or needs a lock on the affected table that would delay your query. I have had a system where the statistics changed the query to a sequential scan instead of an index lookup. Then when they stats were updated the query went back to the index. This just looks like normal multi-body problem fluctuations which can be identified by using much more instrumentation and logging at all layers: network, disk, db logs,... Good luck. Regards, Ken
