3

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?

4
  • Partial index
    – McNets
    Apr 4, 2020 at 22:07
  • Yes, but on what rows? What rows do I put in ( )?
    – lurf jurv
    Apr 4, 2020 at 22:10
  • What other columns do you have in the table? Will you ever restrict on those too? How many rows are true? Apr 4, 2020 at 22:34
  • 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.
    – lurf jurv
    Apr 4, 2020 at 23:16

1 Answer 1

2

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.

3
  • 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.
    – lurf jurv
    Apr 5, 2020 at 0:39
  • @lurfjurv: I would probably include the primary key or some other frequently used columns into the index.
    – user1822
    Apr 5, 2020 at 6:08
  • 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?
    – lurf jurv
    Apr 5, 2020 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.