pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedMulti-column index: Which column order
7+ messages / 4 participants
[nested] [flat]
* Multi-column index: Which column order
@ 2023-02-14 17:53 Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-14 18:47 ` Re: Multi-column index: Which column order Erik Wienhold <ewie@ewie.name>
2023-02-15 08:46 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Sebastien Flaesch @ 2023-02-14 17:53 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
@ 2023-02-14 18:47 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 7+ messages in thread
From: Erik Wienhold @ 2023-02-14 18:47 UTC (permalink / raw)
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general
> On 14/02/2023 18:53 CET Sebastien Flaesch <sebastien.flaesch@4js.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
@ 2023-02-15 08:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-15 16:20 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-02-15 08:46 UTC (permalink / raw)
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-15 08:46 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-02-15 16:20 ` Ron <ronljohnsonjr@gmail.com>
2023-02-16 03:45 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Ron @ 2023-02-15 16:20 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2/15/23 02:46, Laurenz Albe wrote:
[snip]
> 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.
Only on Postgresql?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-15 08:46 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-15 16:20 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
@ 2023-02-16 03:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-16 04:08 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-02-16 03:45 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org
On Wed, 2023-02-15 at 10:20 -0600, Ron wrote:
> On 2/15/23 02:46, Laurenz Albe wrote:
> > 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 more selective column first
> > (which would be "code"), but that is just a myth.
>
> Only on Postgresql?
No, on all relational databases that use B-tree indexes.
Look at how many index entries have to be scanned in both cases, and you will see.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-15 08:46 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-15 16:20 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
2023-02-16 03:45 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-02-16 04:08 ` Ron <ronljohnsonjr@gmail.com>
2023-02-16 10:22 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Ron @ 2023-02-16 04:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2/15/23 21:45, Laurenz Albe wrote:
> On Wed, 2023-02-15 at 10:20 -0600, Ron wrote:
>> On 2/15/23 02:46, Laurenz Albe wrote:
[snip]
>>> Both are the same.
>>> There is an old myth that says that you should use the more selective column first
>>> (which would be "code"), but that is just a myth.
>> Only on Postgresql?
> No, on all relational databases that use B-tree indexes.
Not only is "all" is a very absolute word (I know of a counter-example), but
querying on the second segment means that you have to scan the whole tree
instead of isolating one sub-branch.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Multi-column index: Which column order
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-15 08:46 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-15 16:20 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
2023-02-16 03:45 ` Re: Multi-column index: Which column order Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-16 04:08 ` Re: Multi-column index: Which column order Ron <ronljohnsonjr@gmail.com>
@ 2023-02-16 10:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2023-02-16 10:22 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org
On Wed, 2023-02-15 at 22:08 -0600, Ron wrote:
> On 2/15/23 21:45, Laurenz Albe wrote:
> > On Wed, 2023-02-15 at 10:20 -0600, Ron wrote:
> > > On 2/15/23 02:46, Laurenz Albe wrote:
> > > > Both are the same.
> > > > There is an old myth that says that you should use the more selective column first
> > > > (which would be "code"), but that is just a myth.
> > >
> > > Only on Postgresql?
> >
> > No, on all relational databases that use B-tree indexes.
>
> Not only is "all" is a very absolute word (I know of a counter-example), but
> querying on the second segment means that you have to scan the whole tree
> instead of isolating one sub-branch.
The question was about a multi-column index where all columns are compared
with "equal" in the WHERE condition. For other cases, order matters.
I am aware of the danger of absolute claims, but as far as I can tell, this
is in the nature of B-tree indexes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-02-16 10:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-14 17:53 Multi-column index: Which column order Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-02-14 18:47 ` Erik Wienhold <ewie@ewie.name>
2023-02-15 08:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-15 16:20 ` Ron <ronljohnsonjr@gmail.com>
2023-02-16 03:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-16 04:08 ` Ron <ronljohnsonjr@gmail.com>
2023-02-16 10:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
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