indexing - PostgreSQL composite primary key

Question

In MySQL, when I create a composite primary key, say with columns X, Y, Z, then all three columns become indexes automatically. Does the same happen for Postgres?

Answer 1

If you create a composite primary key, on (x, y, z), PostgreSQL implements this with the help of one UNIQUE multi-column btree index on (x, y, z). In addition, all three columns are NOT NULL (implicitly), which is the main difference between a PRIMARY KEY and a UNIQUE INDEX.

Besides obvious restrictions on your data, the multi-column index also has a somewhat different effect on the performance of queries than three individual indexes on x, y and z.

Related discussion on dba.SE:

• Working of indexes in PostgreSQL

With examples, benchmarks, discussion and outlook on the new feature of index-only scans in Postgres 9.2.

In particular, a primary key on (x, y, z) will speed up queries with conditions on x, (x,y) or (x,y,z) optimally. It will also help with queries on y, z, (y,z) or (x,z) but to a far lesser extent.

If you need to speed up queries on the latter combinations, you may want to change the order of column in your PK constraint and/or create one or more additional indexes. See:

• Is a composite index also good for queries on the first field?