Postgresql – Create partial index with single value

disk-spaceindexindex-tuningperformancepostgresql-12

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?

Best Answer

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

Related Question