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