From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Multi-column index: Which column order
Date: Wed, 15 Feb 2023 09:46:23 +0100
Message-ID: <8780fa45a4041e4229f4a3fa88a61778d8534ab2.camel@cybertec.at> (raw)
In-Reply-To: <DBAP191MB12893BBE46573CA38E0FE921B0A29@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
References: <DBAP191MB12893BBE46573CA38E0FE921B0A29@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
On Tue, 2023-02-14 at 17:53 +0000, Sebastien Flaesch wrote:
> When creating an index on multiple columns, does the order of the columns matter?
> (I guess so)
It does, but not in your case.
> 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)
Both are the same.
There is an old myth that says that you should use the moew selective column first
(which would be "code"), but that is just a myth.
Order makes a difference for queries like "WHERE etb = 'L1' and code LIKE 'ART345'"
or "WHERE code = 'ART345' ORDER BY etb", but not for two equality comparisons.
> Does it depend on the type of index (Btree, GiST, etc) ?
Yes, it does. For B-tree and GiST indexes order matters, for hash, GIN and BRIN
indexes it doesn't (for various reasons).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: laurenz.albe@cybertec.at, sebastien.flaesch@4js.com
Subject: Re: Multi-column index: Which column order
In-Reply-To: <8780fa45a4041e4229f4a3fa88a61778d8534ab2.camel@cybertec.at>
References: <DBAP191MB12893BBE46573CA38E0FE921B0A29@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> <8780fa45a4041e4229f4a3fa88a61778d8534ab2.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox