Re: Slow queries on simple index

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