52

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1467855
  • 3,843
  • 7
  • 28
  • 29
  • 2
    Do you mean MySQL creates **three** *indexes*? Sounds awfully strange –  Jul 05 '12 at 21:19

3 Answers3

127

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So if I add UNIQUE on multiple columns say, `UNIQUE(a,c)`, then it will create a unique index in the background for `(a,c)` as a whole but will it also create indexes for `a` and `c` separately? because later I may need to search only on column `c` or column `a`. Please advice – A l w a y s S u n n y Oct 07 '20 at 10:55
  • @AlwaysSunny: No, only *one* multicolumn index on `(a,c)` is created. But that index can still be used (close to optimally) for searches on `(a)` and (much less efficiently) for searches on `(c)`. Consider an additional index on `(c)` or `(c,a)`. But read the two linked answer above, first. – Erwin Brandstetter Oct 07 '20 at 11:24
4

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

Dondi Michael Stroma
  • 4,668
  • 18
  • 21
-1

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 1
    I think the question was whether each column gets its own index. – Dondi Michael Stroma Jul 05 '12 at 20:54
  • Agree. The question is not if 1 multicolumn index is automatically created but well if 3 single column indexes are created. And the answer is no. Your answer is still relevant as it provides supplementary informations, after being edited of course. In fact, the answer of @dondi-michael-stroma is more relevant than the one which has been chosen by the original requester. – Chucky Sep 23 '14 at 06:27