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

9

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

edited Aug 19, 2010 at 22:46

answered Aug 19, 2010 at 22:07

KeatsPeeks

19.4k5 gold badges56 silver badges84 bronze badges

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

2 Comments

Wells Over a year ago

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?

KeatsPeeks Over a year ago

@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

0

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.

answered Aug 19, 2010 at 22:07

Will A

25k5 gold badges54 silver badges61 bronze badges

Comments

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

0

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.

answered Aug 20, 2010 at 5:38

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

PostgreSQL indices- are these redundant?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related