Date: Thu, 22 Aug 2019 08:19:10 -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: <20190822131910.GC15332@telsasoft.com>
References: 
 <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Thu, Aug 22, 2019 at 02:44:15PM +0200, Marco Colli wrote:
> SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;
> SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS NULL;

>    ->  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)
>          Recheck Cond: (project_id = 123)
>          Rows Removed by Index Recheck: 23746378
>          Heap Blocks: exact=131205 lossy=1480411
>          ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..198324.74 rows=4996823 width=0) (actual time=1582.717..1582.717 rows=4994877 loops=1)

>    ->  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)
>          Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
>          Rows Removed by Index Recheck: 23746273
>          Heap Blocks: exact=131144 lossy=1480409
>          ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..186846.55 rows=4428599 width=0) (actual time=1566.163..1566.163 rows=4994749 loops=1)

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
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).

> Even an approximate count would be enough.

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).

Justin