PostgreSQL composite primary key
composite-primary-key, indexing, postgresql
Solution
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?
Problem
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?