SELECT count(*) from a materialized view with unique index is slow 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)"
"  -&gt;  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.
The first query is logical nonsense for your case:

SELECT count(DISTINCT a.id) from the_view a;

It makes no sense to use DISTINCT with the id defined unique.

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) - unless all rows of a page are marked visible for all transactions (visibility map). 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).

When the whole table needs to be read anyway, it rarely makes no sense to use an index (additionally). So while Postgres may use an index (index-only scan) when counting only parts of a table, a sequential scan is typically faster for a full count. The decision also depends on your cost settings.

You can 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;


In many cases an estimate is good enough and available in close to no time:


Fast way to discover the row count of a table


Remember that every count is hardly more than an estimate. It may be outdated in the moment after you count.

Related page on Slow Counting in the Postgres Wiki.