Обсуждение: multiple indexes on the same column
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
On Fri, 12 Apr 2019 at 11:54, Tiffany Thang <tiffanythang@gmail.com> 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?
Tiffany Thang <tiffanythang@gmail.com> 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
Hi, On 2019-04-12 09:51:51 -0400, Tom Lane wrote: > Tiffany Thang <tiffanythang@gmail.com> 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
Andres Freund <andres@anarazel.de> writes:
> On 2019-04-12 09:51:51 -0400, Tom Lane wrote:
>> Tiffany Thang <tiffanythang@gmail.com> 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
Got it! Thanks Andres and Tom!
Tiff
On Fri, Apr 12, 2019 at 1:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andres Freund <andres@anarazel.de> writes:
> On 2019-04-12 09:51:51 -0400, Tom Lane wrote:
>> Tiffany Thang <tiffanythang@gmail.com> 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