PostgreSQL – Create Empty Partial Index or Equivalent

postgresql

I have a boolean column that will be false for >99.9% of the rows. I need to efficiently fetch all rows where that column is true.

What's the best option? Creating an index on the column? Creating a partial index where the column is true? But I don't know what columns the partial index would / should hold.

This doesn't parse, but is there any way to do something like: CREATE INDEX mytable_cond ON mytable () WHERE cond = TRUE; ? Where the index holds literally zero columns?

Best Answer

You are saying "rows" where I think you mean "columns".

It is not legal to specify an empty list of columns in an index. Just pick a column, preferably one with a short data type. If nothing else, just repeat the same column in both places.

CREATE INDEX mytable_cond ON mytable (cond) WHERE cond = TRUE;

Be careful how you formulate our index and queries. A cond = true query cannot use a partial index defined where cond is true and vice versa, those are not considered equivalent operations.

Related Question