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?

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.