PostgreSQL boolean index:

code:users.sql

create table users (id serial primary key, b_col bool not null);

create index idx_user_b_col ON users (b_col);

code:users

Table "public.users"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+-----------------------------------

id | integer | | not null | nextval('users_id_seq'::regclass)

b_col | boolean | | not null |

Indexes:

"users_pkey" PRIMARY KEY, btree (id)

"idx_user_b_col" btree (b_col)

code:insert.sql

INSERT INTO users (b_col)

SELECT (random() > 0.5) FROM generate_series(1, 500000);

code:explain.sql

postgres=# explain analyze select * from users where b_col is true limit 20;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

Limit (cost=0.00..0.58 rows=20 width=5) (actual time=0.017..0.024 rows=20 loops=1)

-> Seq Scan on users (cost=0.00..7213.00 rows=248700 width=5) (actual time=0.016..0.020 rows=20 loops=1)

Filter: (b_col IS TRUE)

Rows Removed by Filter: 13

Planning Time: 0.126 ms

Execution Time: 0.044 ms

(6 rows)

postgres=# explain analyze select * from users where b_col = true limit 20;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

Limit (cost=0.00..0.58 rows=20 width=5) (actual time=0.023..0.031 rows=20 loops=1)

-> Seq Scan on users (cost=0.00..7213.00 rows=248700 width=5) (actual time=0.021..0.026 rows=20 loops=1)

Filter: b_col

Rows Removed by Filter: 13

Planning Time: 0.358 ms

Execution Time: 0.061 ms

(6 rows)