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.33loops=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).
Thetable 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
takes1-2 seconds.
Kindly,
Lillian
