public inbox for [email protected]
help / color / mirror / Atom feedIndex-Advisor Tools
7+ messages / 6 participants
[nested] [flat]
* Index-Advisor Tools
@ 2017-10-31 17:12 Neto pr <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Neto pr @ 2017-10-31 17:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-10-31 17:19 Anthony Sotolongo <[email protected]>
parent: Neto pr <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Anthony Sotolongo @ 2017-10-31 17:19 UTC (permalink / raw)
To: Neto pr <[email protected]>; +Cc: pgsql-performance
Hi Neto, maybe HypoPG
Can help you:
https://github.com/dalibo/hypopg
El 31 oct. 2017 2:13 PM, "Neto pr" <[email protected]> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-10-31 17:25 Neto pr <[email protected]>
parent: Anthony Sotolongo <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Neto pr @ 2017-10-31 17:25 UTC (permalink / raw)
To: Anthony Sotolongo <[email protected]>; +Cc: pgsql-performance
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 <[email protected]>:
> Hi Neto, maybe HypoPG
> Can help you:
>
> https://github.com/dalibo/hypopg
>
> El 31 oct. 2017 2:13 PM, "Neto pr" <[email protected]> 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
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-10-31 19:25 Alexandre de Arruda Paes <[email protected]>
parent: Neto pr <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Alexandre de Arruda Paes @ 2017-10-31 19:25 UTC (permalink / raw)
To: Neto pr <[email protected]>; +Cc: Anthony Sotolongo <[email protected]>; pgsql-performance
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 <[email protected]>:
> 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 <[email protected]>:
>
>> Hi Neto, maybe HypoPG
>> Can help you:
>>
>> https://github.com/dalibo/hypopg
>>
>> El 31 oct. 2017 2:13 PM, "Neto pr" <[email protected]> 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
>>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-10-31 20:04 Yves Dorfsman <[email protected]>
parent: Neto pr <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Yves Dorfsman @ 2017-10-31 20:04 UTC (permalink / raw)
To: pgsql-performance
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-10-31 20:04 Julien Rouhaud <[email protected]>
parent: Alexandre de Arruda Paes <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Julien Rouhaud @ 2017-10-31 20:04 UTC (permalink / raw)
To: Alexandre de Arruda Paes <[email protected]>; +Cc: Neto pr <[email protected]>; Anthony Sotolongo <[email protected]>; pgsql-performance
On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
<[email protected]> 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 <[email protected]>:
>>
>> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index-Advisor Tools
@ 2017-11-06 18:52 Baron Schwartz <[email protected]>
parent: Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Baron Schwartz @ 2017-11-06 18:52 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Alexandre de Arruda Paes <[email protected]>; Neto pr <[email protected]>; Anthony Sotolongo <[email protected]>; pgsql-performance
On Tue, Oct 31, 2017 at 8:06 PM Julien Rouhaud <[email protected]> wrote:
> On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
> <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-11-06 18:52 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-31 17:12 Index-Advisor Tools Neto pr <[email protected]>
2017-10-31 17:19 ` Anthony Sotolongo <[email protected]>
2017-10-31 17:25 ` Neto pr <[email protected]>
2017-10-31 19:25 ` Alexandre de Arruda Paes <[email protected]>
2017-10-31 20:04 ` Julien Rouhaud <[email protected]>
2017-11-06 18:52 ` Baron Schwartz <[email protected]>
2017-10-31 20:04 ` Yves Dorfsman <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox