CODEWITHSUNDEEP
postgresqlindexing
Vadim Samokhin
Vadim Samokhin

Reputation: 3446

postgres partial index

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?

Upvotes: 3

Views: 844

Answers (2)

Ants Aasma
Ants Aasma

Reputation: 54872

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.

Upvotes: 4

user330315
user330315

Reputation:

My guess(!) is: in the first case (three column index) the column that needs to be evaluated in addition to the existing "index condition" is at the end of the index and therefor the index scan needs to evaluate the condition differently.

In the second case (single column index) the column that is used in the where condition (in addition to the already "indexed condition" is the leading column of the index and can be used more efficiently.

I would expect an index on (c,a,b) to behave the same way (note the different order compared to your three column index).

Upvotes: 1

Related Questions