> On 14/02/2023 18:53 CET Sebastien Flaesch <sebastien.flaesch@xxxxxxx> wrote: > > Hello! > > When creating an index on multiple columns, does the order of the columns > matter? (I guess so) Maybe, depending on the queries. > It's mostly for SELECT statements using a condition that include ALL > columns of the index (pkey): > > SELECT * FROM art WHERE etb='L1' and code='ART345' > > I would naturally put the columns with the most various values first, and > > For example, if the "code" column contains thousands of various item ids > like 'SXZ874', 'ERF345', ... while the "etb" column contains a dozen of > values like "L1", "LT" and "BX". > > Which one is best? > > CREATE UNIQUE INDEX ix1 ON art (code, etb) > or > CREATE UNIQUE INDEX ix1 ON art (etb, code) > > (or its PRIMARY KEY equivalent) It should not make any difference for the query above. It can make a difference for queries that only filter by the second index column or use inequality constraints on those columns. > Does it depend on the type of index (Btree, GiST, etc) ? > > I could not find that information in the doc. Yes, see the documentation on multicolumn indexes with details on how they are used: https://www.postgresql.org/docs/current/indexes-multicolumn.html But you're limited to btree anyway if you're only interested in unique indexes. -- Erik
