Re: Detect missing combined indexes (automatically)

Hi,

On Tue, Jan 15, 2019 at 10:22 AM Thomas Güttler
<guettliml(at)thomas-guettler(dot)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.