Обсуждение: [PERFORM] Index-Advisor Tools
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
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 RegardsNeto
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>:
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_Postgre s_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 RegardsNeto
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 RegardsNeto2017-10-31 15:19 GMT-02:00 Anthony Sotolongo <asotolongo@gmail.com>: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_Postgre s_Advanced_Server_Guide.1.56.h tml Someone would know of other tools for this purpose. I'd appreciate it if you can help me. Best RegardsNeto
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
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
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.