Date: Thu, 22 Aug 2019 13:04:00 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Marco Colli <collimarco91@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow count (simple query, with index)
Message-ID: <20190822180400.GI15332@telsasoft.com>
References: 
 <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
 <20190822131910.GC15332@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20190822131910.GC15332@telsasoft.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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