pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDetect missing combined indexes (automatically)
6+ messages / 4 participants
[nested] [flat]
* Detect missing combined indexes (automatically)
@ 2019-01-10 12:56 Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-12 15:47 ` Re: Detect missing combined indexes (automatically) Jim Finnerty <jfinnert@amazon.com>
2019-01-14 07:19 ` Re: Detect missing combined indexes (automatically) Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 6+ messages in thread
From: Thomas Güttler @ 2019-01-10 12:56 UTC (permalink / raw)
To: pgsql-performance
Is there a way to detect missing combined indexes automatically
I am managing a lot of databases and I think a lot of performance
could get gained.
But I don't want to do this manually.
My focus is on missing combined indexes, since for missing
single indexes there are already tools available.
Regards,
Thomas Güttler
--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Detect missing combined indexes (automatically)
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
@ 2019-01-12 15:47 ` Jim Finnerty <jfinnert@amazon.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jim Finnerty @ 2019-01-12 15:47 UTC (permalink / raw)
To: pgsql-performance
By the term 'combined indexes', do you mean a multi-column index, or a set of
single-column indexes that need to be combined by the planner? What
methodology are you using to recommend missing indexes?
You may be able to enlist help from more people if you provide a specific
example of a query that you have that isn't performing well (with the
explain (analyze, verbose, buffers) plan on https://explain.depesz.com/),
the index(es) that improve performance (with the plan on
https://explain.depesz.com/), and the 'single index' tools / methodology
that you're currently using to suggest missing indexes.
/Jim F
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Detect missing combined indexes (automatically)
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
@ 2019-01-14 07:19 ` Thomas Kellerer <spam_eater@gmx.net>
2019-01-14 07:42 ` Re: Detect missing combined indexes (automatically) Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2019-01-14 07:19 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Thomas Güttler schrieb am 10.01.2019 um 13:56:
> Is there a way to detect missing combined indexes automatically
>
> I am managing a lot of databases and I think a lot of performance
> could get gained.
>
> But I don't want to do this manually.
>
> My focus is on missing combined indexes, since for missing
> single indexes there are already tools available.
The PoWA monitoring tool contains an extension to suggest missing indexes.
I don't know if that includes multi-column indexes though, but it might be worth a try:
https://powa.readthedocs.io/en/latest/stats_extensions/pg_qualstats.html
Thomas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Detect missing combined indexes (automatically)
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-14 07:19 ` Re: Detect missing combined indexes (automatically) Thomas Kellerer <spam_eater@gmx.net>
@ 2019-01-14 07:42 ` Julien Rouhaud <rjuju123@gmail.com>
2019-01-15 09:23 ` Re: Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
0 siblings, 1 reply; 6+ messages in thread
From: Julien Rouhaud @ 2019-01-14 07:42 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jan 14, 2019 at 8:20 AM Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> Thomas Güttler schrieb am 10.01.2019 um 13:56:
> > Is there a way to detect missing combined indexes automatically
> >
> > I am managing a lot of databases and I think a lot of performance
> > could get gained.
> >
> > But I don't want to do this manually.
> >
> > My focus is on missing combined indexes, since for missing
> > single indexes there are already tools available.
>
> The PoWA monitoring tool contains an extension to suggest missing indexes.
>
> I don't know if that includes multi-column indexes though, but it might be worth a try:
>
> https://powa.readthedocs.io/en/latest/stats_extensions/pg_qualstats.html
Yes, it can handle multi-column indexes.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Detect missing combined indexes (automatically)
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-14 07:19 ` Re: Detect missing combined indexes (automatically) Thomas Kellerer <spam_eater@gmx.net>
2019-01-14 07:42 ` Re: Detect missing combined indexes (automatically) Julien Rouhaud <rjuju123@gmail.com>
@ 2019-01-15 09:23 ` Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-15 10:25 ` Re: Detect missing combined indexes (automatically) Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Güttler @ 2019-01-15 09:23 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi Julien Rouhaud,
powa can handle multi-column indexes now? Great news. This must be a new
feature. I checked this roughly one year ago and it was not possible at this time.
Thank you very much powa!
Regards,
Thomas Güttler
Am 14.01.19 um 08:42 schrieb Julien Rouhaud:
> On Mon, Jan 14, 2019 at 8:20 AM Thomas Kellerer <spam_eater@gmx.net> wrote:
>>
>> Thomas Güttler schrieb am 10.01.2019 um 13:56:
>>> Is there a way to detect missing combined indexes automatically
>>>
>>> I am managing a lot of databases and I think a lot of performance
>>> could get gained.
>>>
>>> But I don't want to do this manually.
>>>
>>> My focus is on missing combined indexes, since for missing
>>> single indexes there are already tools available.
>>
>> The PoWA monitoring tool contains an extension to suggest missing indexes.
>>
>> I don't know if that includes multi-column indexes though, but it might be worth a try:
>>
>> https://powa.readthedocs.io/en/latest/stats_extensions/pg_qualstats.html
>
> Yes, it can handle multi-column indexes.
>
--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Detect missing combined indexes (automatically)
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-14 07:19 ` Re: Detect missing combined indexes (automatically) Thomas Kellerer <spam_eater@gmx.net>
2019-01-14 07:42 ` Re: Detect missing combined indexes (automatically) Julien Rouhaud <rjuju123@gmail.com>
2019-01-15 09:23 ` Re: Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
@ 2019-01-15 10:25 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Julien Rouhaud @ 2019-01-15 10:25 UTC (permalink / raw)
To: Thomas Güttler <guettliml@thomas-guettler.de>; +Cc: pgsql-performance@lists.postgresql.org
Hi,
On Tue, Jan 15, 2019 at 10:22 AM Thomas Güttler
<guettliml@thomas-guettler.de> wrote:
>
> Hi Julien Rouhaud,
>
> powa can handle multi-column indexes now? Great news. This must be a new
> feature. I checked this roughly one year ago and it was not possible at this time.
> Thank you very much powa!
Oh, that's unexpected. The first version of the "wizard" (the
"optimize this database" button on the database page) we published was
supposed to handle multi-column indexes. We had few naive tests for
that, so at least some cases were working. What it's doing is
gathering all the quals that have been sampled by pg_qualstats in the
given interval on the given database, and then try to combine them
(possibly merging a single column qual into a multi-column qual),
order them by number of distinct queryid so it can come up with a
quite good set of indexes. So if there are queries with multiple
AND-ed quals on the same table in your workload, it should be able to
suggest a multi-column index. If it doesn't, you should definitely
open a bug on the powa-web repo :)
What it won't do is to suggest to replace a single column index with a
multi-column one, or create a multi-column index if one of the column
is already indexes since only one of the column will be seen as
needing optimization.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-01-15 10:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-10 12:56 Detect missing combined indexes (automatically) Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-12 15:47 ` Jim Finnerty <jfinnert@amazon.com>
2019-01-14 07:19 ` Thomas Kellerer <spam_eater@gmx.net>
2019-01-14 07:42 ` Julien Rouhaud <rjuju123@gmail.com>
2019-01-15 09:23 ` Thomas Güttler <guettliml@thomas-guettler.de>
2019-01-15 10:25 ` Julien Rouhaud <rjuju123@gmail.com>
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