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:
Show quoted text
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