Re: Extremely slow count (simple query, with index)

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 <[email protected]> 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?
>