multiple indexes on the same column

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

multiple indexes on the same column

Tiffany Thang
Hi,
Can someone explain the use of creating multiple indexes on the same column? 

How would the optimizer determine which index to use? From my brief testing, the optimizer picked the latest created index, testidx3. Can you provide a scenario where creating multiple indexes on the same column would be beneficial? 

create table test (a int, b int);
create index testidx1 on test (a);
create index testidx2 on test (a);
create index testidx3 on test (a);


Thanks.

Tiff
Reply | Threaded
Open this post in threaded view
|

Re: multiple indexes on the same column

Geoff Winkless
On Fri, 12 Apr 2019 at 11:54, Tiffany Thang <[hidden email]> wrote:
Can you provide a scenario where creating multiple indexes on the same column would be beneficial? 

When you have too much disk space?
When your table writes are too fast?
Reply | Threaded
Open this post in threaded view
|

Re: multiple indexes on the same column

Tom Lane-2
In reply to this post by Tiffany Thang
Tiffany Thang <[hidden email]> writes:
> Can someone explain the use of creating multiple indexes on the same
> column?

There is none, unless the indexes have different properties (e.g.
different opclasses and/or index AMs).

I'd suggest reading

https://www.postgresql.org/docs/current/indexes.html

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: multiple indexes on the same column

Andres Freund
Hi,

On 2019-04-12 09:51:51 -0400, Tom Lane wrote:
> Tiffany Thang <[hidden email]> writes:
> > Can someone explain the use of creating multiple indexes on the same
> > column?
>
> There is none, unless the indexes have different properties (e.g.
> different opclasses and/or index AMs).

Well, it can be beneficial to create a new index concurrently, and then
drop the old one concurrently. Before v12 that's the only way to
recreate an index during production, if it e.g. bloated.

Greetings,

Andres Freund


Reply | Threaded
Open this post in threaded view
|

Re: multiple indexes on the same column

Tom Lane-2
Andres Freund <[hidden email]> writes:
> On 2019-04-12 09:51:51 -0400, Tom Lane wrote:
>> Tiffany Thang <[hidden email]> writes:
>>> Can someone explain the use of creating multiple indexes on the same
>>> column?

>> There is none, unless the indexes have different properties (e.g.
>> different opclasses and/or index AMs).

> Well, it can be beneficial to create a new index concurrently, and then
> drop the old one concurrently.

Right, but in that situation there's no intent to keep both indexes
in place.  You're just putting up with extra overhead temporarily
as a means to avoid taking an exclusive lock.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: multiple indexes on the same column

Tiffany Thang
Got it! Thanks Andres and Tom!

Tiff

On Fri, Apr 12, 2019 at 1:07 PM Tom Lane <[hidden email]> wrote:
Andres Freund <[hidden email]> writes:
> On 2019-04-12 09:51:51 -0400, Tom Lane wrote:
>> Tiffany Thang <[hidden email]> writes:
>>> Can someone explain the use of creating multiple indexes on the same
>>> column?

>> There is none, unless the indexes have different properties (e.g.
>> different opclasses and/or index AMs).

> Well, it can be beneficial to create a new index concurrently, and then
> drop the old one concurrently.

Right, but in that situation there's no intent to keep both indexes
in place.  You're just putting up with extra overhead temporarily
as a means to avoid taking an exclusive lock.

                        regards, tom lane