public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lillian Berry <[email protected]>
To: Ken Marshall <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Slow queries on simple index
Date: Sat, 17 Jan 2026 20:33:32 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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

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