Does cardinality matter when chosing the order of the composite key columns?

Question

I have a table

Invoice(PosId,InvoiceId,Name,...) with PK: (PosId, InvoiceId)

and a detail table

InvoiceEntry(PosId, InvoiceId, EntryId, Product, Price)

• The cardinality of PosId is 10 range: [1...10]
• The cardinality of InvoiceIdis 1,000,000 range [1...999999]

When doing joins both PosId and InvoiceId are always used. (There is no usecase where someone would use only one of the two columns when querying)

Does the cardinality of each column play a role on which one of the two I should pick as the first column of the primary key?

Answer 1

No, this is an old myth. The order doesn't matter as long as both columns are compared with =.

If one column is compared with the equality operator and the other with a different operator (<, <=, >, >=, IN), put the column that is compared with = first to avoid scanning unnecessary index entries.

Answer 2

Compound PostgreSQL index should always start with the column with more distinct values. Cardinality matters.