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."
Also, how many million rows is this table in total? Have you considered partitioning?
