*Postgres version: PostgreSQL 10.3 on x86_64-apple-darwin16.7.0*
*Operating system and version: MacOS v10.12.6*
*How you installed PostgreSQL: Homebrew*
I have a table as defined below. The table contains 1,027,616 rows, 50,349
of which have state='open' and closed IS NULL. Since closed IS NULL for all
rows where state='open', I want to remove the unnecessary state column.
```
CREATE TABLE tickets (
id bigserial primary key,
title character varying,
description character varying,
state character varying,
closed timestamp,
created timestamp,
updated timestamp,
last_comment timestamp,
size integer NOT NULL,
comment_count integer NOT NULL
);
CREATE INDEX "state_index" ON "tickets" ("state") WHERE ((state)::text =
'open'::text));
```
As part of the process of removing the state column, I am trying to index
the closed column so I can achieve equal query performance (index scan) as
when I query on the state column as shown below:
```
EXPLAIN ANALYZE select title, created, closed, updated from tickets where
state = 'open';
Index Scan using state_index on tickets (cost=0.29..23430.20 rows=50349
width=64) (actual time=17.221..52.110 rows=51533 loops=1)
Planning time: 0.197 ms
Execution time: 56.255 ms
```
However, when I index the closed column, a bitmap scan is used instead of
an index scan, with slightly slower performance. Why isn't an index scan
being used, given that the exact same number of rows are at play as in my
query on the state column? How do I index closed in a way where an index
scan is used?
```
CREATE INDEX closed_index ON tickets (id) WHERE closed IS NULL;
VACUUM ANALYZE tickets;
EXPLAIN ANALYZE select title, created, closed, updated from tickets where
closed IS NULL;
Bitmap Heap Scan on tickets (cost=824.62..33955.85 rows=50349 width=64)
(actual time=10.420..56.095 rows=51537 loops=1)
Recheck Cond: (closed IS NULL)
Heap Blocks: exact=17478
-> Bitmap Index Scan on closed_index (cost=0.00..812.03 rows=50349
width=0) (actual time=6.005..6.005 rows=51537 loops=1)
Planning time: 0.145 ms
Execution time: 60.266 ms
```
