How to create an "empty" partial index, or equivalent, in Postgres?

Question

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?

• McNets: Partial index
• lurf jurv: Yes, but on what rows? What rows do I put in ( )?
• Colin 't Hart: What other columns do you have in the table? Will you ever restrict on those too? How many rows are true?
• lurf jurv: No, I only need to fetch where this one boolean column is true. There are many other columns. I have some other complicated indexes for the other common query. Less than one in a thousand rows will have this column as true.

Answer 1

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.

• lurf jurv: Thanks, you are correct, I have edited my question to say "columns". I will just make a partial index like you say on the column itself, where the value is true.
• user1822: @lurfjurv: I would probably include the primary key or some other frequently used columns into the index.
• lurf jurv: I'm curious; why? Is it so that the query can become index-only if I'm only fetching a few of the columns? Or another reason?
• Matt Whitlock: You don't have to use columns as the key fields of your index; you can use expressions (including literal constants!) as well. I did this for one of my tables: CREATE INDEX cash_flow_items_unpaid_idx ON cash_flow_items ((true)) WHERE paid IS DISTINCT FROM accrued;. (Most of my cash_flow_items rows have paid = accrued, but I want to index the rows where that is not the case.) The doubled parentheses around true are necessary to get the parser to parse it as an expression rather than as a column name.