Detect missing combined indexes (automatically)

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

Detect missing combined indexes (automatically)

Thomas Güttler
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

Reply | Threaded
Open this post in threaded view
|

Re: Detect missing combined indexes (automatically)

Jim Finnerty
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

Jim Finnerty, AWS, Amazon Aurora PostgreSQL
Reply | Threaded
Open this post in threaded view
|

Re: Detect missing combined indexes (automatically)

Thomas Kellerer
In reply to this post by Thomas Güttler
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




Reply | Threaded
Open this post in threaded view
|

Re: Detect missing combined indexes (automatically)

Julien Rouhaud
On Mon, Jan 14, 2019 at 8:20 AM Thomas Kellerer <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Detect missing combined indexes (automatically)

Thomas Güttler
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Detect missing combined indexes (automatically)

Julien Rouhaud
Hi,

On Tue, Jan 15, 2019 at 10:22 AM Thomas Güttler
<[hidden email]> 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.