Re: Detect missing combined indexes (automatically)

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.