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.
SET enable_seqscan = off
? Your question started by stating that you don't get an index scan when you setenable_seqscan = off
. If it'son
, the question is not originally as asked but instead "why am I not getting an index scan when seq scan is enabled?" That's a far more difficult question to answer, but ultimately it'll boil down to "planner estimates" (which are likely correct). You'll have toEXPLAIN ANALYZE
both the query with and without seq scan, and provide a lot more information.explain (analyze, buffers)
of the two original plans (the Seq Scan and the Bitmap Index Scan)