Performance on SELECT with composite primary key's element

Question

I'm working with PostgreSQL 8.1 and I have a table with a composite primary key of 3 elements and no other indexes: PRIMARY KEY (el1, el2, el3)

If I make a select operation like SELECT * FROM table WHERE el1 IN (...), will the operation take into account the fact that the primary key contains el1 or it will be slower because there is no index that can help?

Thanks.

Have a go at this post. http://stackoverflow.com/questions/11352056/postgresql-composite-primary-key — Gopinagh.R, Jan 07 '13 at 14:10
8.1 has been out of support for several years now. You should upgrade immediately to a supported version (ideally 9.2) — , Jan 07 '13 at 14:19
Upgrading to 9.1 is not an option right now but we are taking this idea into account. Thanks. — Cristian, Jan 07 '13 at 15:11

score 2 · Accepted Answer · answered Jan 07 '13 at 14:13

According to the PostgreSQL documentation:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.

You can also try running an explain plan on your query to determine this behavior.

score 0 · Answer 2 · answered Jan 07 '13 at 14:13

Postgres -- and every SQL engine I know of -- can use the first one or more fields from a multi-field index. In your case, where the index is on (el1, el2, el3), it can use the index for a query like "where el1=x" or "where el1=x and el2=y".

It cannot use the index if you don't give a value for el1. Like "select ... where el2=y", the index would be useless.

Performance on SELECT with composite primary key's element

2 Answers2