Обсуждение: Is there any tool which will help me run and explain analyze about 150 queries?

Поиск
Список
Период
Сортировка

Is there any tool which will help me run and explain analyze about 150 queries?

От
kunwar singh
Дата:
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?

От
Achilleas Mantzios
Дата:

Prepend "EXPLAIN ANALYZE " on every statement :

cat foo.sql  | awk '{print " EXPLAIN (ANALYZE, BUFFERS, TIMING, SUMMARY) " $0}' | psql testdb -f -

Στις 7/4/23 20:57, ο/η kunwar singh έγραψε:
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
-- 
Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt

Re: Is there any tool which will help me run and explain analyze about 150 queries?

От
kyle Hailey
Дата:

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 <krishsingh.111@gmail.com> 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?

От
kunwar singh
Дата:
Thanks Kyle. I am also trying to get it working :). 

@Archilleas ,Thanks for your inputs. Appreciate it. I am further  interested in learning if we can automate the generation/creation of  foo.sql by including queries with bind variables defined and bind values populated ( speaking in Oracle linguistics , pardon my Postgres ignorance, a newbie here)

On Fri, Apr 7, 2023 at 2:41 PM kyle Hailey <kylelf@gmail.com> wrote:

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 <krishsingh.111@gmail.com> 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


--
Cheers,
Kunwar

Re: Is there any tool which will help me run and explain analyze about 150 queries?

От
Jerry Sievers
Дата:
kunwar singh <krishsingh.111@gmail.com> 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
bothversions 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 <<EOF >$file.explain-output 2>&1
explain analyze
$(<$file)
EOF
done

Re: Is there any tool which will help me run and explain analyze about 150 queries?

От
kyle Hailey
Дата:

Just wrote up my experiences setting up auto_explain on Google Cloud SQL to get explain analyze:





On Fri, Apr 7, 2023 at 9:40 PM Jerry Sievers <gsievers19@comcast.net> wrote:
kunwar singh <krishsingh.111@gmail.com> 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 <<EOF >$file.explain-output 2>&1
explain analyze
$(<$file)
EOF
done


Re: Is there any tool which will help me run and explain analyze about 150 queries?

От
kunwar singh
Дата:
Thanks, I'll check it out!

On Sat, Apr 8, 2023 at 5:45 PM kyle Hailey <kylelf@gmail.com> wrote:

Just wrote up my experiences setting up auto_explain on Google Cloud SQL to get explain analyze:





On Fri, Apr 7, 2023 at 9:40 PM Jerry Sievers <gsievers19@comcast.net> wrote:
kunwar singh <krishsingh.111@gmail.com> 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 <<EOF >$file.explain-output 2>&1
explain analyze
$(<$file)
EOF
done




--
Cheers,
Kunwar

Re: Is there any tool which will help me run and explain analyze about 150 queries?

От
Michael Christofides
Дата:

> 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 hadn't considered using it for this, and it's not free, but our pgMustard scoring API[1] might make your comparative analysis easier.

If you send plans to it (in JSON format), you'll get back summary info like total time and total buffers, as well as scored tips. You can then compare the summary info, tips, and scores to find those with the biggest differences, and some starting points for what the issue(s) may be.