Date: Sat, 17 Jan 2026 09:28:17 -0600
From: Ken Marshall <ktm@rice.edu>
To: Lillian Berry <lillian@star-ark.net>
Cc: pgsql-performance@lists.postgresql.org, infra@pluralkit.me
Subject: Re: Slow queries on simple index
Message-ID: <aWuqkaIOchIB7aR7@customer.dllstxx1.isp.starlink.com>
References: <aaf503fd-c08c-465d-9d80-4a71abb722c1@app.fastmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <aaf503fd-c08c-465d-9d80-4a71abb722c1@app.fastmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aWuqkaIOchIB7aR7%40customer.dllstxx1.isp.starlink.com>
Precedence: bulk

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