Search Postgresql Archives

Multi-column index: Which column order

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello!

When creating an index on multiple columns, does the order of the columns matter?
(I guess so)

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)

Does it depend on the type of index (Btree, GiST, etc) ?

I could not find that information in the doc.

Seb

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux