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 — user330315
– user330315, Commented Sep 13, 2012 at 10:34

Ants Aasma · Accepted Answer · 2012-09-13 15:09:44Z

4

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.

answered Sep 13, 2012 at 15:09

Ants Aasma

55.3k16 gold badges98 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2012-09-13 10:45:09Z

1

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).

answered Sep 13, 2012 at 10:45

user330315

1 Comment

Vadim Samokhin Over a year ago

> I would expect an index on (c,a,b) to behave the same way... And it does, unlike MySQL ;)

Collectives™ on Stack Overflow

postgres partial index

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related