Re: Q on SQL Performance tuning
On Sun, 27 Jan 2019 at 06:29, legrand legrand wrote: > > Hi, > > There are many tools: > - (core) extension pg_stat_statements will give you informations of SQL > executions, I've had enormous success using pg_stat_statements and gathering the data over time in Prometheus. That let me build a dashboard in Grafana that can dive into specific queries and see when their executions rate suddenly spiked or the resource usage for the query suddenly changed. > - extension pg_stat_sql_plans (alpha) gives all of pg_stat_statements and much more Extending pg_stat_statements to track statistics per-plan would be a huge advance. And being able to link the metrics with data dumped in the log from things like log_min_duration and pg_auto_explain would make them both more useful. -- greg
Re: Q on SQL Performance tuning
On Sun, Jan 27, 2019 at 08:43:15AM +, Bhupathi, Kaushik (CORP) wrote: > 2) Is there anyway to know the historical execution plan details of a > particular SQL ? Per my understanding so far since there is no concept of > shared pool unlike Oracle every execution demands a new hard parse. However > wanted to check with experts to know if any extension available on this? There's also autoexplain, althought I think that's typically configured to only output plans for queries which longer than a minimum duration. Justin
Re: Q on SQL Performance tuning
Hi, There are many tools: - (core) extension pg_stat_statements will give you informations of SQL executions, - extension pgsentinel https://github.com/pgsentinel/pgsentinel gives the same results as Oracle ASH view - java front end PASH viewer https://github.com/dbacvetkov/PASH-Viewer gives a nice view of CPU IO per query - extension pg_stat_sql_plans (alpha) gives all of pg_stat_statements and much more (parsing time, planid, plan text, ...) Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
