Postgres 13.6 is using Sequential scan and Postgres 10.18 is using Index Only Scan

Question

We have upgraded our db from version 10.18 to 13.6 and for simple count select I have noticed a different behaviour.

Postgres 13.6:

explain select count(id) from jobs;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=176452.97..176452.98 rows=1 width=8)
   ->  Gather  (cost=176452.75..176452.96 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=175452.75..175452.76 rows=1 width=8)
               ->  Parallel Seq Scan on jobs  (cost=0.00..173388.60 rows=825660 width=8)

Postgres 10.18:

explain select count(*) from jobs;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=162999.57..162999.58 rows=1 width=8)
   ->  Gather  (cost=162999.35..162999.56 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=161999.35..161999.36 rows=1 width=8)
               ->  Parallel Index Only Scan using jobs_pkey on jobs  (cost=0.43..155962.19 rows=2414866 width=0)

Not sure why on newer version we have only seq scan instead of index scan

Answer 1

Probably because you did not VACUUM the table, which is what makes index-only scans fast.

Other explanations may be that on the v13 database, random_page_cost is set to a higher value.

Perhaps the sequential scan is actually faster. You can test that by running with enable_seqscan set to on and off.