postgres partial index

Question

I have a table with about 30 columns, column a integer not null, b boolean not null and c integer not null among them. And there is a query that is often run with a = 2 and b is true and c = <something>, i.e.

select * from my_table where a = 2 and b is true and c = 3215

The question is: should I include a and b columns in partial index, like this:

CREATE INDEX idx_my_table_a_b_c
ON my_table 
USING btree  (a, b, c)
WHERE a = 2 AND b IS TRUE;

or should I not, like this:

CREATE INDEX idx_my_table_a_b_c
ON my_table 
USING btree  (c)
WHERE a = 2 AND b IS TRUE;

In the first case explain outputs

"Index Scan using idx_my_table_a_b_c on my_table  (cost=0.00..8.27 rows=1 width=4007)"
"  Index Cond: ((b = true) AND (a = 2))"

and in the second Index cond part is absent

"Index Scan using idx_my_table_a_b_c on my_table  (cost=0.00..8.27 rows=1 width=4007)"

And by the way what does Index cond mean?

Please show us the select statement that corresponds to the explain output — a_horse_with_no_name

Ants Aasma Ants Aasma · Accepted Answer · 2012-09-13T15:09:44

If your index predicate is strict equality then there is no point in including the predicate columns in the index. Every index entry will have the same values for the columns and so won't contribute to index lookup, they will only slow down inserts and lookups and bloat the index.

postgres partial index

2 Answers