> > 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?
