Thread: [PERFORM] Index-Advisor Tools

[PERFORM] Index-Advisor Tools

From
Neto pr
Date:

Hello All I'm researching on Index-Advisor Tools to be applied in SQL queries. At first I found this: - EnterpriseDB - https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html Someone would know of other tools for this purpose. I'd appreciate it if you can help me.

Best Regards
Neto

Re: [PERFORM] Index-Advisor Tools

From
Anthony Sotolongo
Date:
Hi Neto,  maybe 

HypoPG

Can help you:


El 31 oct. 2017 2:13 PM, "Neto pr" <netopr9@gmail.com> escribió:

Hello All I'm researching on Index-Advisor Tools to be applied in SQL queries. At first I found this: - EnterpriseDB - https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html Someone would know of other tools for this purpose. I'd appreciate it if you can help me.

Best Regards
Neto

Re: [PERFORM] Index-Advisor Tools

From
Neto pr
Date:
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.

Best Regards
Neto

2017-10-31 15:19 GMT-02:00 Anthony Sotolongo <asotolongo@gmail.com>:
Hi Neto,  maybe 

HypoPG

Can help you:


El 31 oct. 2017 2:13 PM, "Neto pr" <netopr9@gmail.com> escribió:

Hello All I'm researching on Index-Advisor Tools to be applied in SQL queries. At first I found this: - EnterpriseDB - https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html Someone would know of other tools for this purpose. I'd appreciate it if you can help me.

Best Regards
Neto

Re: [PERFORM] Index-Advisor Tools

From
Alexandre de Arruda Paes
Date:
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@gmail.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.

Best Regards
Neto

2017-10-31 15:19 GMT-02:00 Anthony Sotolongo <asotolongo@gmail.com>:
Hi Neto,  maybe 

HypoPG

Can help you:


El 31 oct. 2017 2:13 PM, "Neto pr" <netopr9@gmail.com> escribió:

Hello All I'm researching on Index-Advisor Tools to be applied in SQL queries. At first I found this: - EnterpriseDB - https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html Someone would know of other tools for this purpose. I'd appreciate it if you can help me.

Best Regards
Neto


Re: [PERFORM] Index-Advisor Tools

From
Yves Dorfsman
Date:
I have not used it yet, but from the presentation, very promising:

https://medium.com/@ankane/introducing-dexter-the-automatic-indexer-for-postgres-5f8fa8b28f27

https://github.com/ankane/dexter

-- 
https://yves.zioup.com
gpg: 4096R/32B0F416 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Index-Advisor Tools

From
Julien Rouhaud
Date:
On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
<adaldeia@gmail.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@gmail.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/.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Index-Advisor Tools

From
Baron Schwartz
Date:
On Tue, Oct 31, 2017 at 8:06 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
<adaldeia@gmail.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).

As a vendor, I normally stay silent on this list, but I feel compelled to speak here. This is a feature we built support for in VividCortex. (I'm the founder and CEO). Unlike most PostgreSQL monitoring tools, our product not only aggregates query activity into metrics, but retains a rich and representative sample set of the actual statements that executed, including full parameters (even for prepared statements), and all of the properties for the query: the connection's origin, the timestamp, latency, etc. These are mapped visually to a scatterplot, and you can instantly see where there are clusters of latency outliers, etc, and inspect those quickly. It includes EXPLAIN plans and everything else you need to understand how that statement executed. VividCortex may not be suitable for your scenario, but our customers do use it frequently for finding queries that need indexes and determining what indexes to add.