PostgreSQL composite primary key

by

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:

More Related Contents:

  1. PostgreSQL LIKE query performance variations
  2. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  3. Why does PostgreSQL perform sequential scan on indexed column?
  4. Do nullable columns occupy additional space in PostgreSQL?
  5. Does a Postgres UNIQUE constraint imply an index?
  6. Difference between GiST and GIN index
  7. Postgres query optimization (forcing an index scan)
  8. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  9. Optimize Postgres query on timestamp range
  10. Multicolumn index on 3 fields with heterogenous data types
  11. How to add a conditional unique index on PostgreSQL
  12. Can PostgreSQL index array columns?
  13. Use multiple conflict_target in ON CONFLICT clause
  14. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  15. How to find table creation time?
  16. Go and IN clause in Postgres
  17. How to add “on delete cascade” constraints?
  18. Is there a way to disable function overloading in Postgres
  19. Migrate database from Postgres to MySQL [closed]
  20. How can I start PostgreSQL on Windows?
  21. PostgreSQL – max number of parameters in “IN” clause?
  22. SQLAlchemy, Psycopg2 and Postgresql COPY
  23. PostgreSQL array of elements that each are a foreign key
  24. How to reset sequence in postgres and fill id column with new data?
  25. Postgres ERROR: could not open file for reading: Permission denied
  26. Configure JPA to let PostgreSQL generate the primary key value
  27. ERROR: unterminated quoted string at or near
  28. Is it possible to change the natural order of columns in Postgres?
  29. ERROR: could not stat file “XX.csv”: Unknown error
  30. SELECT or INSERT a row in one command

Leave a Comment