Re: [PERFORM] Index-Advisor Tools
On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes 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 : >> >> 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
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
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 : > 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 : > >> Hi Neto, maybe HypoPG >> Can help you: >> >> https://github.com/dalibo/hypopg >> >> El 31 oct. 2017 2:13 PM, "Neto pr" 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 >>> >> >
Re: [PERFORM] Index-Advisor Tools
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 : > Hi Neto, maybe HypoPG > Can help you: > > https://github.com/dalibo/hypopg > > El 31 oct. 2017 2:13 PM, "Neto pr" 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 >> >
Re: [PERFORM] Index-Advisor Tools
Hi Neto, maybe HypoPG Can help you: https://github.com/dalibo/hypopg El 31 oct. 2017 2:13 PM, "Neto pr" 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 >
[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
[PERFORM] Massive insert vs heavy contention in LWLock:buffer_content
I'm working on an application which performs a lot of inserts in 2 large
tables.
Previously we didn't know about lwlocks, but we're now testing in Amazon RDS
Aurora - PostgreSQL (9.6.3).
In previous load tests, both local servers and classic Amazon RDS, there was
some scalability limit we couldn't find - CPU / memory / IO were all low,
but still there was contention that wasn't visible in PostgreSQL views.
Now with Aurora it shows that most of the sessions are blocking on
LWLock:buffer_content.
I would like some insights, as we have 2 tables with ~35 million rows each,
and they have several indexes (shown below).
This request is a crucial operation for our system, and each application
request must insert on those 2 large tables in a single transaction, plus
some other selects.
I've searched a lot and found nothing on how to mitigate this issue. Just
found that it might be related to inserts.
Any tips?
For reference, here are the descriptions of both tables:
\d transactions
Tabela
"public.transactions"
Coluna |Tipo
| Modificadores
---+-+---
id| bigint
| não nulo valor padrão de nextval('transactions_id_seq'::regclass)
subclass | character varying(31)
|
amount| numeric
| não nulo
authorization_status | character varying(255)
|
date | timestamp without time
zone | não nulo
description | text
|
transaction_feedback_expiration_notified | boolean
|
transaction_feedback_expiration_reminder_notified | boolean
|
transaction_feedback_reminder_notified| boolean
|
by_id | bigint
|
channel_id| bigint
| não nulo
feedback_id | bigint
|
from_user_id | bigint
|
next_authorization_level_id | bigint
|
to_user_id| bigint
|
type_id | bigint
| não nulo
order_id | bigint
|
status| character varying(255)
|
received | boolean
|
principal_type_id | bigint
|
access_client_id | bigint
|
original_transfer_id | bigint
|
show_to_receiver | boolean
|
expiration_date | timestamp without time
zone |
scheduled | boolean
|
first_installment_immediate | boolean
|
installments_count| integer
|
process_date | timestamp without time
zone |
comments | text
|
transaction_id| bigint
|
sms_code | character varying(255)
|
external_principal_value | character varying(255)
|
external_principal_type_id| bigint
|
received_by_id| bigint
|
from_name | character varying(255)
|
to_name | character varying(255)
|
next_occurrence_date | timestamp without time
zone |
occurrences_count | integer
|
occurrence_interval_amount| integer
|
occurrence_interval_field | character varying(255)
|
last_occurrence_failure_id| bigint
|
last_occurrence_success_id| bigint
