Postgres version
Using PostgreSQL 10.3.
Table definition
CREATE TABLE tickets (
id bigserial primary key,
state character varying,
closed timestamp
);
CREATE INDEX "state_index" ON "tickets" ("state")
WHERE ((state)::text = 'open'::text));
Cardinality
The table contains 1027616 rows, with 51533 of the rows having state = 'open'
and closed IS NULL
, or 5%.
A query with a condition on state
performs well using an index scan as expected:
explain analyze select * from tickets where state = 'open';
Index Scan using state_index on tickets (cost=0.29..16093.57 rows=36599 width=212) (actual time=0.025..22.875 rows=37346 loops=1)
Planning time: 0.212 ms
Execution time: 25.697 ms
I am trying to achieve the same or better performance for the query with the condition closed IS NULL
so that I can drop the state
column and rely on the closed
column for fetching the same rows. closed
is null
for the same rows where state = 'open'
, hence the state
column is redundant.
select * from tickets where closed IS NULL;
However, none of the indexes I've tried result in a single index scan like the first query. Below are the indexes I've tried along with the EXPLAIN ANALYZE
results.
A partial index:
CREATE INDEX "closed_index" ON "tickets" ("closed") WHERE (closed IS NULL)
explain analyze select * from tickets where closed IS NULL;
Bitmap Heap Scan on tickets (cost=604.22..38697.91 rows=36559 width=212) (actual time=12.879..48.780 rows=37348 loops=1)
Recheck Cond: (closed IS NULL)
Heap Blocks: exact=14757
-> Bitmap Index Scan on closed_index (cost=0.00..595.09 rows=36559 width=0) (actual time=7.585..7.585 rows=37348 loops=1)
Planning time: 4.831 ms
Execution time: 52.068 ms
An expression index:
CREATE INDEX "closed_index" ON "tickets" ((closed IS NULL))
explain analyze select * from tickets where closed IS NULL;
Seq Scan on tickets (cost=0.00..45228.26 rows=36559 width=212) (actual time=0.025..271.418 rows=37348 loops=1)
Filter: (closed IS NULL)
Rows Removed by Filter: 836578
Planning time: 7.992 ms
Execution time: 274.504 ms
A partial expression index:
CREATE INDEX "closed_index" ON "tickets" ((closed IS NULL))
WHERE (closed IS NULL);
explain analyze select * from tickets where closed IS NULL;
Bitmap Heap Scan on tickets (cost=604.22..38697.91 rows=36559 width=212) (actual time=177.109..238.008 rows=37348 loops=1)
Recheck Cond: (closed IS NULL)
Heap Blocks: exact=14757
-> Bitmap Index Scan on "closed_index" (cost=0.00..595.09 rows=36559 width=0) (actual time=174.598..174.598 rows=37348 loops=1)
Planning time: 23.063 ms
Execution time: 241.292 ms
UPDATED
Expanded table definition:
CREATE TABLE tickets (
id bigserial primary key,
state character varying,
closed timestamp,
created timestamp,
updated timestamp,
title character varying,
size integer NOT NULL,
comment_count integer NOT NULL
);
CREATE INDEX "state_index" ON "tickets" ("state")
WHERE ((state)::text = 'open'::text));
Cardinality:
The table contains 1027616 rows, with 51533 of the rows having state = 'open' and closed IS NULL, or 5%. As mentioned above, I am trying to drop the state
column so I want to be able to fetch the same rows using a condition on the closed
column instead.
Query with condition on state
column uses index scan.
explain analyze select id, title, created, closed, updated from tickets where state = 'open';
Index Scan using state_index on tickets (cost=0.29..22901.58 rows=49356 width=72) (actual time=0.107..49.599 rows=51533 loops=1)
Planning time: 0.511 ms
Execution time: 54.366 ms
I'd like the same performance (index scan, ideally) when switching to querying on the closed
column. I tried the partial index on id
and closed IS NULL
:
CREATE INDEX closed_index ON tickets (id) WHERE closed IS NULL;
VACUUM ANALYZE tickets;
explain analyze select id, title, created, closed, updated from tickets where closed IS NULL;
Bitmap Heap Scan on tickets (cost=811.96..33999.42 rows=49461 width=72) (actual time=7.868..47.080 rows=51537 loops=1)
Recheck Cond: (closed IS NULL)
Heap Blocks: exact=17479
-> Bitmap Index Scan on closed_index (cost=0.00..799.60 rows=49461 width=0) (actual time=4.868..4.868 rows=51537 loops=1)
Planning time: 0.222 ms
Execution time: 51.028 ms
closed
column is necessary.state
column, as its only state is represented inclosed IS NULL / NOT NULL
, is the optimal solution. One more detail: the 2nd query of your addition is actually faster (51 ms >> 54 ms). So the problem to address does not seem to be there to begin with. Still wondering where that index scan came from. And why the 2nd query hasrows=51537
while the 1st hasrows=51533
. More inconsistencies?