PostgreSQL indices- are these redundant?

Question

CREATE INDEX alias_pub_idx2 ON info.palias USING btree (publisher_id, player_id, pub_player_id); CREATE INDEX alias_pub_idx3 ON info.palias USING btree (player_id);

The first includes the three columns; the latter includes only the one. I'm thinking they are redundant- that the first btree index is sufficient, but I'm not terribly familiar w/ the PostgreSQL indexing methods. Thoughts?

Is that valid? Last I heard, PostgreSQL didn't support covering indexes, which is what the alias_pub_idx2 is. — OMG Ponies
– OMG Ponies, Commented Aug 19, 2010 at 22:01
That's what I initially thought, OMG - but they're not covering indexes - info.palias must be schema.table syntax. — Will A
– Will A, Commented Aug 19, 2010 at 22:05

KeatsPeeks · Accepted Answer · 2010-08-19 22:46:53Z

PostgreSQL multicolumn indexes are "ordered", which means this would be redundant only if player_id was the first column mentioned in alias_pub_idx2.

This, however, would be redundant :

CREATE INDEX alias_pub_idx2 ON info.palias USING btree (publisher_id, player_id, pub_player_id); CREATE INDEX alias_pub_idx3 ON info.palias USING btree (publisher_id); /* first column of another index => waste of space */

So it right to say that if player_id is the column more joined on (by far), it should either be the first column in the index, or have its own standalone index?
@Wells yes, that's right. From the documentation: " the index is most efficient when there are constraints on the leading (leftmost) columns"

Will A · Accepted Answer · 2010-08-19 22:07:49Z

Neither index makes the other redundant - a query against info.palias that looks for particular player_ids would favour the second index over the first if publisher_id is not also a criteria.

Frank Heikens · Accepted Answer · 2010-08-20 05:38:53Z

USING btree

BTREE indexes are ordered, that's why your second index is redundant: The first column is the same and can be used in all queries where player_id is a condition.

Collectives™ on Stack Overflow

PostgreSQL indices- are these redundant?

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related