pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Multi-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>
  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 18:47  Erik Wienhold <ewie@ewie.name>
  parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
  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-15 08:46  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Sebastien Flaesch <sebastien.flaesch@4js.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-15 16:20  Ron <ronljohnsonjr@gmail.com>
  parent: 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-16 03:45  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: 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-16 04:08  Ron <ronljohnsonjr@gmail.com>
  parent: 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-16 10:22  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Ron <ronljohnsonjr@gmail.com>
  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