Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL I am currently working on a small presentation showing when postgresql will switch from index scan to bitmap scan to sequence scan.
First example (hint all index scan…):
Loosely based on a similar example of Schönig in Mastering Postgres by Packt.
create table t_test (id serial, name text);

-- add data
insert into t_test (name) select 'hans' from generate_series(1, 5000000);
insert into t_test (name) select 'paul' from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

--Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9) 
explain SELECT * FROM t_test WHERE id = 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9)
explain SELECT * FROM t_test WHERE id &lt; 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..21.77 rows=419 width=9)
explain SELECT * FROM t_test WHERE id &lt; 423;

So this example is not very good in showing when postgres will switch from index scan to bitmap scan.
explain SELECT * FROM t_test WHERE id &lt; 6002353;
At around 6 mio retrieved lines pg will switch do seq scan.
Second example:
drop table if exists t_test;

create table t_test (id numeric, name text);

-- add data
insert into t_test (name, id) select 'hans', random() * 10000 from generate_series(1, 5000000);
insert into t_test (name, id) select 'paul', random() * 10000 from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

-- Index Scan using idx_x on t_test  (cost=0.43..8.45 rows=1 width=16)
explain SELECT * FROM t_test WHERE id = 42353;

-- Seq Scan on t_test  (cost=0.00..179078.41 rows=9999873 width=16)
explain SELECT * FROM t_test WHERE id &lt; 42353;

-- Bitmap Heap Scan on t_test  (cost=9955.65..69408.57 rows=429834 width=16)
explain SELECT * FROM t_test WHERE id &lt; 423;


So this example is exactly what I wanted. But why the difference?
The example is almost identical. But I had to change the id.
You’re asking why the two queries with the WHERE id &lt; 423 clause have different query plans correct?
Well the type of scan used is dependent on the actual characteristics of the table. Your first table uses a serial so it increments by 1 and there are only 423 rows to get so an index scan is most efficient. In your second example there you use a numeric and multiply that by a random so there are potentially quite a few rows in that filter and PG decides an index scan will not be most efficient.
TL;DR even though these queries and table properties look similar, your tables’ data is actually quite different.