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,
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
``` 
