0

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?

0

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

| improve this answer | |
  • ok, thanks, but what if i need to select *? – csny Jun 29 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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