Filter/Partial INDEX

Content:

Inspired by a LinkedIn post.

Some databases including PostgreSQL and MS SQLServer have a feature called filter index or partial index.

Let us create a database table:

CREATE TABLE todo (
    id INTEGER NOT NULL,
    task VARCHAR(50),
    status INTEGER,
    PRIMARY KEY(id)
);
-- insert 100000 rows with 99% status=1 and 1% status=0

Query cost:

EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 1; -- seq scan, aggregated cost 2134
EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 0; -- seq scan, aggregated cost 1889

No surprise. Let us try to speed it up with an index:

CREATE INDEX ix_todo_status ON todo (status);
EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 1; -- seq scan, aggregated cost 2134
EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 0; -- index only scan, aggregated cost 25

The index is worthless for status=1 because that is 99% of rows (and the database does not even attempt to use the index), but it really speed up for status=0 because that is only 1%.

But if the database does not use the status=1 entries, then there are no point in storing them on disk.

This is where filter/partial index come in:

CREATE INDEX ix_todo_status ON todo (status) WHERE status = 0;
EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 1; -- seq scan, aggregated cost 2134
EXPLAIN SELECT COUNT(*) FROM todo WHERE status = 0; -- index only scan, aggregated cost 26

Same improvement but much smaller index.

Nice trick. Obviously only relevant for rare cases. But still nice to know.

Comments: