Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS0Kg-0000yT-JP for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 18:47:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pS0Kf-0002bF-G0 for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 18:47:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS0Kf-0002b6-53 for pgsql-general@lists.postgresql.org; Tue, 14 Feb 2023 18:47:13 +0000 Received: from mout-u-107.mailbox.org ([80.241.59.207]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS0KY-0002Ip-H7 for pgsql-general@postgresql.org; Tue, 14 Feb 2023 18:47:12 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4PGVYg1kfvz9sRJ; Tue, 14 Feb 2023 19:47:03 +0100 (CET) Date: Tue, 14 Feb 2023 19:47:02 +0100 (CET) From: Erik Wienhold To: Sebastien Flaesch , "pgsql-general@postgresql.org" Message-ID: <824167798.67572.1676400422583@office.mailbox.org> In-Reply-To: References: Subject: Re: Multi-column index: Which column order MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4PGVYg1kfvz9sRJ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 14/02/2023 18:53 CET Sebastien Flaesch 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