Index scan on UNIQUE index for count(*)

Question

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
  ->  Bitmap Heap Scan on t  (cost=210923.32..897114.38 rows=23176906 width=0)
        ->  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,
   <10 original cols>
);

insert into tmp (row_id, <10 cols>) select row_id, <10 cols> 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
  ->  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
  ->  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
        ->  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.

Answer 1

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)
   ->  Gather  (cost=10633.33..10633.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8)
               ->  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)
   ->  Gather  (cost=26616.76..26616.97 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=25616.76..25616.77 rows=1 width=8)
               ->  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"

Answer 2

_{Community wiki answer:}

Your question is addressed by the Is "count(*)" much faster now? section of the PostgreSQL wiki page about Index-only scans.

A traditional complaint made of PostgreSQL, generally when comparing it unfavourably with MySQL (at least when using the MyIsam storage engine, which doesn't use MVCC) has been "count(*) is slow". Index-only scans can be used to satisfy these queries without there being any predicate to limit the number of rows returned, and without forcing an index to be used by specifying that the tuples should be ordered by an indexed column. However, in practice that isn't particularly likely.

It is important to realise that the planner is concerned with minimising the total cost of the query. With databases, the cost of I/O typically dominates. For that reason, "count(*) without any predicate" queries will only use an index-only scan if the index is significantly smaller than its table. This typically only happens when the table's row width is much wider than some indexes'.

An index-only scan will have to visit the heap tuples if visibility cannot be determined by looking at the visibility map. There is a big 'it depends' when checking which case yours is. Under optimal circumstances, you'll get an index-only scan. Otherwise, if the tuples have to be checked for visibility, a sequential scan quickly becomes the winner, as it does not have the overhead of checking the index first.

You get a bitmap_heap_scan with enable seq_scan=off, so the planner behaves as advertised:

It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available.

The details are in other sections of the same wiki page.

Answer 3

An index only scan is not going to look very attractive unless most of the table's pages are marked as all-visible. So you need to VACUUM the table.

PostgreSQL's default autovacuum settings are tuned to prevent bloat, not to keep index-only scans happy, so it may require manual intervention to keep the table well-enough vacuumed for your purpose.

Note that it still might prefer the full table scan. The index only scan is more CPU intensive than the full table scan, and its IO can be more random and less sequential. If the table is 20 times larger than the index, it will probably prefer the index-only scan, but at 6 times larger it is a bit of a toss up.