I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-5% of the table. It is frequently updated. The table can be big (>1B rows)

I have frequent queries for numCol=0 and boolCol is false, so I created a partial btree index:

myindex(numCol,boolCol) where numCol = 0 and boolCol is false.

Although the query performance is good, this index will have a single value, and I think that btree might consume more disk space than necessary.

Is there a more suitable index type for this case? What is the best practice in such cases?

1 Answer 1


A partial index typically doesn't need the columns from the WHERE clause in the index as well. You should put other columns that you retrieve in the queries that make use of that index in the column. e.g. the PK or something else.

So if you e.g. frequently use this query:

select pk_column, other_column
from the_table
where num_col = 0
and bool_col = false;

Then it usually makes sense to create the index like this:

create index on the_table (pk_column, other_column)
where num_col = 0
and bool_col = false;

That way the index scan can be used to also get the values needed for the

  • ok, thanks, but what if i need to select *?
    – csny
    Commented Jun 29, 2020 at 20:00

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.