How can one make a partial index to optimize a query for a single value?

Question

Let's say I have table foos with column thing_id.

What's the smallest index I could make for these queries?

select count(*) from foos where thing_id is null;
select id from foos where thing_id is null;

Conceptually, we just need the index to keep track of a list of primary keys which match the criteria. Is it possible/useful to create a partial index on... no columns?

I tried this and postgres considered it a syntax error:

create index on foos where thing_id is null;

This did work

create index on foos (thing_id) where thing_id is null;

But will this result in unnecessarily writing the value of thing_id (always NULL) for each row?

• Akina: CREATE INDEX idx_name ON foos ((thing_id IS NULL), id) seems to be the most suitable for these queries. Of course you may try also CREATE INDEX idx_name ON foos (id) WHERE thing_id IS NULL.

Answer 1

You always need a column in an index.

You could try to index a constant that occupies only one byte:

SELECT typname FROM pg_type WHERE typlen = 1;
 typname 
---------
 bool
 char
(2 rows)

If you choose boolean, that would be

CREATE INDEX ON foos ((TRUE)) WHERE thing_id IS NULL;

But my recommendation is to index id. Sure, that would make the index somewhat bigger, but your second query could use a much faster index-only scan.

• John Bachir: gotcha, so create index on foos (id) where thing_id is null;
• Laurenz Albe: I see that I forgot the table name - I have fixed it. Yes, you got it right.