I have completely solved (from 17s to 1s) by running this command:
vacuum analyze subscriptions;
Now I run the autovacuum more frequently using these settings in postgresql.conf:
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
Thanks to everyone - and in particular to Justin Pryzby for pointing me in the right direction.
On Thu, Aug 22, 2019 at 7:37 PM Michael Lewis <mlewis@entrata.com> wrote:
You can SELECT reltuples FROM pg_class WHERE oid='subscriptions'::oid, but its accuracy depends on frequency of vacuum (and if a large delete/insert happened since the most recent vacuum/analyze).
This only seems helpful to find approx. count for the entire table, without considering the WHERE condition.
Marco,
As Justin pointed out, you have most of your time in the bitmap heap scan. Are you running SSDs? I wonder about tuning effective_io_concurrency to make more use of them.
"Currently, this setting only affects bitmap heap scans."