Re: Detect missing combined indexes (automatically)
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 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
Re: Detect missing combined indexes (automatically)
Hi, On Tue, Jan 15, 2019 at 10:22 AM Thomas Güttler 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.
No matching tables have ever been vacuumed
Hi team, We have enabled the monitoring to monitor the vacuuming of tables via check_postgres_last_vacuum plugin but we are getting the below warning message. Notification Type: PROBLEM Service: PostgreSQL last vacuum () Host Alias: vmshowcasedb2.vpc.prod.scl1.us.tribalfusion.net Address: 10.26.12.89 State: UNKNOWN Info: POSTGRES_LAST_VACUUM UNKNOWN: DB postgres (host:vmshowcasedb2.vpc.prod.scl1.us.tribalfusion.net) No matching tables have ever been vacuumed Kindly suggest how we can overcome on this. Regards, Daulat
