public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ken Marshall <[email protected]>
To: Lillian Berry <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Slow queries on simple index
Date: Sat, 17 Jan 2026 09:28:17 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Slow queries on simple index
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox