pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there any tool which will help me run and explain analyze about 150 queries?
8+ messages / 5 participants
[nested] [flat]
* Is there any tool which will help me run and explain analyze about 150 queries?
@ 2023-04-07 17:57 kunwar singh <krishsingh.111@gmail.com>
2023-04-07 18:19 ` 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-07 18:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kyle Hailey <kylelf@gmail.com>
2023-04-08 04:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? Jerry Sievers <gsievers19@comcast.net>
0 siblings, 3 replies; 8+ messages in thread
From: kunwar singh @ 2023-04-07 17:57 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
@ 2023-04-07 18:19 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2 siblings, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2023-04-07 18:19 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
@ 2023-04-07 18:40 ` kyle Hailey <kylelf@gmail.com>
2023-04-07 20:48 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: kyle Hailey @ 2023-04-07 18:40 UTC (permalink / raw)
To: kunwar singh <krishsingh.111@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2023-04-07 18:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kyle Hailey <kylelf@gmail.com>
@ 2023-04-07 20:48 ` kunwar singh <krishsingh.111@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: kunwar singh @ 2023-04-07 20:48 UTC (permalink / raw)
To: kyle Hailey <kylelf@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
@ 2023-04-08 04:40 ` Jerry Sievers <gsievers19@comcast.net>
2023-04-08 21:44 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kyle Hailey <kylelf@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Jerry Sievers @ 2023-04-08 04:40 UTC (permalink / raw)
To: kunwar singh <krishsingh.111@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2023-04-08 04:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? Jerry Sievers <gsievers19@comcast.net>
@ 2023-04-08 21:44 ` kyle Hailey <kylelf@gmail.com>
2023-04-09 22:15 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: kyle Hailey @ 2023-04-08 21:44 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: kunwar singh <krishsingh.111@gmail.com>; pgsql-performance@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2023-04-08 04:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? Jerry Sievers <gsievers19@comcast.net>
2023-04-08 21:44 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kyle Hailey <kylelf@gmail.com>
@ 2023-04-09 22:15 ` kunwar singh <krishsingh.111@gmail.com>
2023-04-12 09:17 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? Michael Christofides <michael@pgmustard.com>
0 siblings, 1 reply; 8+ messages in thread
From: kunwar singh @ 2023-04-09 22:15 UTC (permalink / raw)
To: kyle Hailey <kylelf@gmail.com>; +Cc: Jerry Sievers <gsievers19@comcast.net>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Is there any tool which will help me run and explain analyze about 150 queries?
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2023-04-08 04:40 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? Jerry Sievers <gsievers19@comcast.net>
2023-04-08 21:44 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kyle Hailey <kylelf@gmail.com>
2023-04-09 22:15 ` Re: Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
@ 2023-04-12 09:17 ` Michael Christofides <michael@pgmustard.com>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Christofides @ 2023-04-12 09:17 UTC (permalink / raw)
To: kunwar singh <krishsingh.111@gmail.com>; +Cc: kyle Hailey <kylelf@gmail.com>; Jerry Sievers <gsievers19@comcast.net>; pgsql-performance@lists.postgresql.org
> > 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-04-12 09:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-07 17:57 Is there any tool which will help me run and explain analyze about 150 queries? kunwar singh <krishsingh.111@gmail.com>
2023-04-07 18:19 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2023-04-07 18:40 ` kyle Hailey <kylelf@gmail.com>
2023-04-07 20:48 ` kunwar singh <krishsingh.111@gmail.com>
2023-04-08 04:40 ` Jerry Sievers <gsievers19@comcast.net>
2023-04-08 21:44 ` kyle Hailey <kylelf@gmail.com>
2023-04-09 22:15 ` kunwar singh <krishsingh.111@gmail.com>
2023-04-12 09:17 ` Michael Christofides <michael@pgmustard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox