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 <mle...@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." > > https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR > > Also, how many million rows is this table in total? Have you considered > partitioning? >
