Feedback-ID: i8db94900:Fastmail
MIME-Version: 1.0
Date: Sat, 17 Jan 2026 20:33:32 +0100
From: "Lillian Berry" <lillian@star-ark.net>
To: "Ken Marshall" <ktm@rice.edu>
Cc: pgsql-performance@lists.postgresql.org, infra@pluralkit.me
Message-Id: <ccc224a4-9f2d-4eaa-844e-f376d58e9bd8@app.fastmail.com>
In-Reply-To: <aWuqkaIOchIB7aR7@customer.dllstxx1.isp.starlink.com>
References: <aaf503fd-c08c-465d-9d80-4a71abb722c1@app.fastmail.com>
 <aWuqkaIOchIB7aR7@customer.dllstxx1.isp.starlink.com>
Subject: Re: Slow queries on simple index
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ccc224a4-9f2d-4eaa-844e-f376d58e9bd8%40app.fastmail.com>
Precedence: bulk

Hi,

> You may want to try auto_explain to try and catch the explain for one of
> these queries.

I enabled auto_explain and it was very informative.

LOG:  duration: 2202.924 ms  execute <unnamed>: SELECT * FROM "members" WHERE "hid" = $1
LOG:  duration: 2202.918 ms  plan:
        Query Text: SELECT * FROM "members" WHERE "hid" = $1
        Query Parameters: $1 = '<redacted>'
        Gather  (cost=1000.00..907603.54 rows=67083 width=832) (actual time=1222.570..2202.893 rows=1.00 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          Buffers: shared hit=816042
          ->  Parallel Seq Scan on members  (cost=0.00..899895.24 rows=27951 width=832) (actual time=1719.931..1972.703 rows=0.33 loops=3)
                Filter: ((hid)::text = '<redacted>'::text)
                Rows Removed by Filter: 4463227
                Buffers: shared hit=816042

It's not using the indexes at all.

Since the query is clearly the same as I attempted earlier, why would it not be using the index?

This table gets reasonably frequent inserts, but the insert timestamps don't really match up with the slow queries (for instance, there can be a 30-second gap where no rows are inserted, but there is still a slow query every few seconds). The table gets infrequent deletes.

Most if not all activity on this table is only updating single rows, or deleting based on the "system" foreign key column.
There is a job that calculates the count of rows (select count(*) from members), but it only runs once a minute and takes 1-2 seconds.

Kindly,
Lillian