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?

Answer 1

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 */

Answer 2

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.

Answer 3

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.