I have a materialized view with a unique index (on the id column)
and both of these queries are slow (they scan the whole table):
SELECT count(DISTINCT a.id) from the_view a;
SELECT count(*) from the_view a;
the plan is:
"QUERY PLAN"
"Aggregate (cost=5303367.00..5303367.01 rows=1 width=0)"
" -> Seq Scan on the_view a (cost=0.00..4953116.20 rows=140100320 width=0)"
I'm surprised that the index is not being used.
If id is defined UNIQUE NOT NULL, it makes no sense to add DISTINCT:
SELECT count(DISTINCT a.id) from the_view a;
The second query is typically faster with a sequential scan:
SELECT count(*) from the_view a;
Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check visibility in the main relation (the heap / the table) And a MATERIALIZED VIEW is basically just another table with functionally dependent data and a recipe how to derive it from other tables (the stored query).
The exception being an index-only scan, which is possible for data pages that are marked visible for all transactions in the visibility map. That's only competitive if the index is substantially smaller than the table (i.e. fewer data pages to read).
When the whole table needs to be read anyway, it makes no sense to use an index additionally. Postgres is much more likely to use an index-only scan when counting only parts of a table. Driving estimations (also) depend on your cost settings.
Verify that Postgres can in fact use an index-only scan (or a bitmap index scan) by disabling plan types (only in your session for debugging!). The manual instructs in the chapter Examining Index Usage:
When indexes are not used, it can be useful for testing to force their use. There are run-time parameters that can turn off various plan types (see Section 18.7.1). For instance, turning off sequential scans (
enable_seqscan) and nested-loop joins (enable_nestloop), which are the most basic plans, will force the system to use a different plan. If the system still chooses a sequential scan or nested-loop join then there is probably a more fundamental reason why the index is not being used;
Often an estimate is good enough and available in close to no time. see:
Unless your table (or MV) is read-only every count can be outdated the moment you get it.
Related page on Slow Counting in the Postgres Wiki.
