Re: Is there any tool which will help me run and explain analyze about 150 queries?
In my opinion, Datadog is the best Postgres monitor available, but it doesn't have a feature that has been discussed. However, you can use auto_explain to analyze long-running queries by setting a limit. I recently enabled it in GCP Cloud SQL, but I haven't seen any results in the logs yet. I need to figure out which parameters to enable to get it working. On Fri, Apr 7, 2023 at 10:57 AM kunwar singh wrote: > Hi Listers, > Anyone here use such a tool for Postgres? Any recommendations? > > Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres > 15. I want to run explain analyze for 150 in both versions for comparative > analysis. > > I am looking for the easiest way to do it with a tool :) > > -- > Cheers, > Kunwar >
Re: Is there any tool which will help me run and explain analyze about 150 queries?
Just wrote up my experiences setting up auto_explain on Google Cloud SQL to get explain analyze: https://www.kylehailey.com/post/auto_explain-on-google-cloud-sql-gcp On Fri, Apr 7, 2023 at 9:40 PM Jerry Sievers wrote: > kunwar singh writes: > > > Hi Listers, > > Anyone here use such a tool for Postgres? Any recommendations? > > > > Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres > 15. I want to run explain analyze for 150 in both versions for comparative > > analysis. > > > > I am looking for the easiest way to do it with a tool :) > > I'd use a tool like bash for this which is very affordable :-) > > Just load your queries into individual files in some directory with a > .sql suffix... > > for file in $some-directory/*.sql; do > psql <$file.explain-output 2>&1 > explain analyze > $(<$file) > EOF > done > > >
Re: How do Monitoring tools capture explain plan of a query
Datadog runs explain on a subset of queries , should be most of the top queries, and doesn't use auto_explain (though there is an request to use it if it is already set up) Kyle On Sat, Apr 22, 2023 at 8:29 AM kunwar singh wrote: > Postgres noob question. > > For example say datadog > https://docs.datadoghq.com/database_monitoring/query_metrics/#explain-plans > > Do they run EXPLAIN on all queries? > Or is it automatic in postgres? Like when auto_explain is enabled? > > > -- > Cheers, > Kunwar >
Re: lwlock:LockManager wait_events
late to the game here but this might be of interest: https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/ On Fri, Oct 25, 2024 at 5:10 AM SAMEER KUMAR wrote: > > > On Fri, 25 Oct 2024, 14:36 James Pang, wrote: > >> experts, >> we faced into a lot of lwlock:LockManager wait-events , all of these >> queries are "select ..." , there are other several session are doing DML, >> insert/update/delete on same table. Did these DML transactions holding >> "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ? >> > > > Which version of PostgreSQL? > Are these all same query or variant of same query (with different > parameters)? > > What's the schema (you can redact column names) for the tables involved in > the select query which is blocked/waiting on lock_manager? > > >> Thanks, >> >> James >> >
