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
