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