Index scan on UNIQUE index for count(*) I've got a table t with ~23 million rows (4248 MB in size). There's a column row_id in it, with a not null constraint. And a unique index p1 on t(row_id). 

When I do select count(*) from t to count all the rows in the table, the planner tells me: 

Seq Scan on t  (cost=0.00..686191.06 rows=23176906 width=0) 


I would have expected a fast Index Only Scan (index p1 occupies only 698 MB - 6x less). 

If I do SET enable_seqscan = off, then the planner still insists on reading the table rows:

QUERY PLAN
  -&gt;  Bitmap Heap Scan on t  (cost=210923.32..897114.38 rows=23176906 width=0)
        -&gt;  Bitmap Index Scan on p1  (cost=0.00..205129.09 rows=23176906 width=0)


Why is the unique index ignored in this case? What's the catch?

I am using PostgreSQL 10.4

For a clean room test I did the following:

create table tmp
(
  row_id      varchar(15) unique not null,
   &lt;10 original cols&gt;
);

insert into tmp (row_id, &lt;10 cols&gt;) select row_id, &lt;10 cols&gt; from t;
commit;
analyze tmp;

set enable_seqscan = on;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=744070.45..744070.46 rows=1 width=8) (actual time=5631.501..5631.502 rows=1 loops=1)
  Buffers: shared hit=209109 read=245254
  -&gt;  Seq Scan on tmp  (cost=0.00..686128.96 rows=23176596 width=0) (actual time=0.014..3481.967 rows=23176906 loops=1)
        Buffers: shared hit=209109 read=245254
Planning time: 0.064 ms
Execution time: 5631.531 ms


SET enable_seqscan = off;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=980282.14..980282.15 rows=1 width=8) (actual time=16224.408..16224.408 rows=1 loops=1)
  Buffers: shared hit=26285 read=542015
  -&gt;  Bitmap Heap Scan on tmp  (cost=236211.69..922340.65 rows=23176596 width=0) (actual time=10030.115..14157.288 rows=23176906 loops=1)
        Heap Blocks: exact=454363
        Buffers: shared hit=26285 read=542015
        -&gt;  Bitmap Index Scan on tmp_row_id_key  (cost=0.00..230417.54 rows=23176596 width=0) (actual time=9929.582..9929.582 rows=23176906 loops=1)
              Buffers: shared hit=26285 read=87652
Planning time: 0.051 ms
Execution time: 16229.303 ms


No parallel index scan so far. PostgreSQL insists on accessing the table for some obscured reason.
As to why you do not get an index scan with SET enable_seqscan = off, you should be getting an index-only scan. Your situation can not yet be recreated with the data you've provided. This certainly works with PostgreSQL 10.4. I can't speak to your own use case, and there are a lot of reasons why you may not get an index scan in the real world. Ultimately, debugging a question along those lines will result in an answer that is simply "planner estimates" but will require a lot more data about your environment, configuration, and the plans with and without SET enable_seqscan = off.

Sample Data

BEGIN;
  CREATE TABLE foo ( x int NOT NULL UNIQUE );
  INSERT INTO foo (x) SELECT generate_series(1,1e6);
COMMIT;

ANALYZE foo; -- don't forget to analyze


With seq_scan

test=# EXPLAIN SELECT count(*) FROM foo;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8)
   -&gt;  Gather  (cost=10633.33..10633.54 rows=2 width=8)
         Workers Planned: 2
         -&gt;  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8)
               -&gt;  Parallel Seq Scan on foo  (cost=0.00..8591.67 rows=416667 width=0)
(5 rows)


Notice we're doing a "Parallel Seq Scan"

Without seq_scan

SET enable_seq_scan = off;

test=# EXPLAIN SELECT count(*) FROM foo;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=26616.97..26616.98 rows=1 width=8)
   -&gt;  Gather  (cost=26616.76..26616.97 rows=2 width=8)
         Workers Planned: 2
         -&gt;  Partial Aggregate  (cost=25616.76..25616.77 rows=1 width=8)
               -&gt;  Parallel Index Only Scan using foo_x_key on foo  (cost=0.42..24575.09 rows=416667 width=0)
(5 rows)


Notice we're doing a "Parallel Index Only Scan"