On Thu, Aug 22, 2019 at 07:54:57PM +0200, Marco Colli wrote: > I have completely solved (from 17s to 1s) by running this command: > vacuum analyze subscriptions; Thanks for following though. On Thu, Aug 22, 2019 at 08:19:10AM -0500, Justin Pryzby wrote: > You can see it used the same index in both cases, and the index scan was > reasonably fast (compared to your goal), but the heap component was slow. > > I suggest to run VACUUM FREEZE on the table, to try to encourage index only > scan. If that works, you should condider setting aggressive autovacuum I should've used a better word, since aggressive means something specific. Perhaps just: "parameter to encourage more frequent autovacuums". > parameter, at least for the table: > ALTER TABLE subscriptions SET (AUTOVACUUM_VACUUM_SCALE_FACTOR=0.005); > -- And possibly lower value of autovacuum_freeze_max_age > > Or, running manual vacuum possibly during quiet hours (possibly setting > vacuum_freeze_table_age to encourage aggressive vacuum). I think my reference to autovacuum_freeze_max_age and vacuum_freeze_table_age were incorrect; what's important is "relallvisible" and not "relfrozenxid". And xid wraparound isn't at issue here. > > Even an approximate count would be enough. > > You can SELECT reltuples FROM pg_class WHERE oid='subscriptions'::oid, but its Should be: oid='subscriptions'::regclass > accuracy depends on frequency of vacuum (and if a large delete/insert happened > since the most recent vacuum/analyze). Justin
