Обсуждение: Detect missing combined indexes (automatically)
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
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
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
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.
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
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.