Re: Index-Advisor Tools

On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
<adaldeia(at)gmail(dot)com> wrote:
> I will be very happy with a tool(or a stats table) that shows the most
> searched values from a table(since a statistic reset). i.e.:
>
> table foo (id int, year int)
>
> top 3 searched value for year field: 2017(500x), 2016(300x), 2015(55x)
>
> With this info we can create partial indexes or do a table partitioning.
>
>
>
> 2017-10-31 15:25 GMT-02:00 Neto pr <netopr9(at)gmail(dot)com>:
>>
>> Thanks for reply Antony.
>> But from what I've read, HYPOPG only allows you to create hypothetical
>> indexes, so the DBA can analyze if it brings benefits.
>> What I would like is a tool that from a SQL Query indicates which indexes
>> would be recommended to decrease the response time.

powa + pg_qualstats will give you this kind of information, and it can
analyse the actual queries and suggest indexes that could boost them,
or show constant repartition for the different WHERE clauses.

You can get more information on http://powa.readthedocs.io/en/latest/.