Horton

Thread

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

    kunwar singh <krishsingh.111@gmail.com> — 2023-04-07T17:57:32Z

    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

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

    Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2023-04-07T18:19:36Z

    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

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

    kyle Hailey <kylelf@gmail.com> — 2023-04-07T18:40:47Z

    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
>

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

    kunwar singh <krishsingh.111@gmail.com> — 2023-04-07T20:48:59Z

    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

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

    Jerry Sievers <gsievers19@comcast.net> — 2023-04-08T04:40:36Z

    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

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

    kyle Hailey <kylelf@gmail.com> — 2023-04-08T21:44:28Z

    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 <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
>
>
>

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

    kunwar singh <krishsingh.111@gmail.com> — 2023-04-09T22:15:33Z

    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:
>
> https://www.kylehailey.com/post/auto_explain-on-google-cloud-sql-gcp
>
>
>
>
> 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

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

    Michael Christofides <michael@pgmustard.com> — 2023-04-12T09:17:30Z

    > > 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.

[1]: https://www.pgmustard.com/docs/scoring-api