PostgreSQL composite primary key

问题:

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?

回答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 have to be NOT NULL, of course, which is the main difference between a PRIMARY KEY and a UNIQUE INDEX.

Besides the 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.

We had a very thorough discussion about that recently on dba.SE in this related question. With examples, benchmarks, discussion and outlook to the upcoming feature of index-only scans in version 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 or create one or more additional indexes.

回答2:

Yes:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

回答3:

No, you get one index for the three-column primary key.