Horton

Thread

  1. Index-Advisor Tools

    Neto pr <netopr9@gmail.com> — 2017-10-31T17:12:03Z

    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

  2. Re: Index-Advisor Tools

    Anthony Sotolongo <asotolongo@gmail.com> — 2017-10-31T17:19:58Z

    Hi Neto,  maybe HypoPG
Can help you:

https://github.com/dalibo/hypopg

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
>

  3. Re: Index-Advisor Tools

    Neto pr <netopr9@gmail.com> — 2017-10-31T17:25:55Z

    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:
>
> https://github.com/dalibo/hypopg
>
> 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 Regards
>> Neto
>>
>

  4. Re: Index-Advisor Tools

    Alexandre Arruda <adaldeia@gmail.com> — 2017-10-31T19:25:36Z

    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:
>>
>> https://github.com/dalibo/hypopg
>>
>> 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 Regards
>>> Neto
>>>
>>
>

  5. Re: Index-Advisor Tools

    Yves Dorfsman <yves@zioup.com> — 2017-10-31T20:04:03Z

    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

  6. Re: Index-Advisor Tools

    Julien Rouhaud <rjuju123@gmail.com> — 2017-10-31T20:04:12Z

    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/.

  7. Re: Index-Advisor Tools

    Baron Schwartz <baron.schwartz@gmail.com> — 2017-11-06T18:52:44Z

    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.