pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedExact same output - pg_stat_statements
6+ messages / 4 participants
[nested] [flat]
* Exact same output - pg_stat_statements
@ 2022-12-30 16:04 Rushikesh socha <rushikesh.s@gmail.com>
2022-12-30 20:24 ` Re: Exact same output - pg_stat_statements Ron <ronljohnsonjr@gmail.com>
2023-01-02 13:34 ` Re: Exact same output - pg_stat_statements hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rushikesh socha @ 2022-12-30 16:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi, Whenever I am running the below query on one of my Azure PostgreSQL
PaaS instances I am getting exact same output. I feel it shows old
information but as far as i know pg_stat_statements only shows current
information and not past right ? It may be a bug?
SELECT dbid,queryid,substring(query, 1, 50) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time / sum(total_time::numeric) OVER
())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
I have erased the query text from the screenshot below. but Please look at
the rest of the columns which are exactly the same. How it is possible ?
[image: image.png]
Thanks.
Attachments:
image.png (image/png, 204.9K) download
view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Exact same output - pg_stat_statements
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
@ 2022-12-30 20:24 ` Ron <ronljohnsonjr@gmail.com>
2022-12-31 17:26 ` Re: Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ron @ 2022-12-30 20:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
The screen shots are hard to see. Too small. (Imgur links might be better.)
On 12/30/22 10:04, Rushikesh socha wrote:
> Hi, Whenever I am running the below query on one of my Azure PostgreSQL
> PaaS instances I am getting exact same output. I feel it shows old
> information but as far as i know pg_stat_statements only shows current
> information and not past right ? It may be a bug?
>
> SELECT dbid,queryid,substring(query, 1, 50) AS short_query,
> round(total_time::numeric, 2) AS total_time,
> calls,
> round(mean_time::numeric, 2) AS mean,
> round((100 * total_time / sum(total_time::numeric) OVER
> ())::numeric, 2) AS percentage_cpu
> FROM pg_stat_statements
> ORDER BY total_time DESC
> LIMIT 20;
>
> I have erased the query text from the screenshot below. but Please look at
> the rest of the columns which are exactly the same. How it is possible ?
>
> image.png
>
>
> Thanks.
> *
> *
>
--
Born in Arizona, moved to Babylonia.
Attachments:
image.png (image/png, 204.9K) download
view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Exact same output - pg_stat_statements
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
2022-12-30 20:24 ` Re: Exact same output - pg_stat_statements Ron <ronljohnsonjr@gmail.com>
@ 2022-12-31 17:26 ` Rushikesh socha <rushikesh.s@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rushikesh socha @ 2022-12-31 17:26 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Just trying to show that the query output is exactly the same anytime I
run.
Thanks.
On Fri, Dec 30, 2022 at 3:24 PM Ron <ronljohnsonjr@gmail.com> wrote:
> The screen shots are hard to see. Too small. (Imgur links might be
> better.)
>
> On 12/30/22 10:04, Rushikesh socha wrote:
>
> Hi, Whenever I am running the below query on one of my Azure PostgreSQL
> PaaS instances I am getting exact same output. I feel it shows old
> information but as far as i know pg_stat_statements only shows current
> information and not past right ? It may be a bug?
>
> SELECT dbid,queryid,substring(query, 1, 50) AS short_query,
> round(total_time::numeric, 2) AS total_time,
> calls,
> round(mean_time::numeric, 2) AS mean,
> round((100 * total_time / sum(total_time::numeric) OVER
> ())::numeric, 2) AS percentage_cpu
> FROM pg_stat_statements
> ORDER BY total_time DESC
> LIMIT 20;
>
> I have erased the query text from the screenshot below. but Please look at
> the rest of the columns which are exactly the same. How it is possible ?
>
> [image: image.png]
>
>
> Thanks.
>
>
>
> --
> Born in Arizona, moved to Babylonia.
>
Attachments:
image.png (image/png, 204.9K) download
view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Exact same output - pg_stat_statements
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
@ 2023-01-02 13:34 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-01-03 05:52 ` Re: Exact same output - pg_stat_statements Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2023-01-02 13:34 UTC (permalink / raw)
To: Rushikesh socha <rushikesh.s@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Fri, Dec 30, 2022 at 11:04:59AM -0500, Rushikesh socha wrote:
> Hi, Whenever I am running the below query on one of my Azure PostgreSQL
> PaaS instances I am getting exact same output. I feel it shows old
> information but as far as i know pg_stat_statements only shows current
> information and not past right ? It may be a bug?
pg_stat_statements has all the data since last reset of stats.
So if you never reset stats, it accumulated data for howeve rlong you
are using pg.
Not really surprising that top total-time uses are the same.
If you want to sensibly use it you should call reset function every now
and then.
Best regards,
depesz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Exact same output - pg_stat_statements
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
2023-01-02 13:34 ` Re: Exact same output - pg_stat_statements hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-01-03 05:52 ` Julien Rouhaud <rjuju123@gmail.com>
2023-01-09 10:15 ` Re: Exact same output - pg_stat_statements hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 6+ messages in thread
From: Julien Rouhaud @ 2023-01-03 05:52 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@depesz.com>; +Cc: Rushikesh socha <rushikesh.s@gmail.com>; pgsql-general@lists.postgresql.org
On Mon, Jan 02, 2023 at 02:34:13PM +0100, hubert depesz lubaczewski wrote:
> On Fri, Dec 30, 2022 at 11:04:59AM -0500, Rushikesh socha wrote:
> > Hi, Whenever I am running the below query on one of my Azure PostgreSQL
> > PaaS instances I am getting exact same output. I feel it shows old
> > information but as far as i know pg_stat_statements only shows current
> > information and not past right ? It may be a bug?
>
> pg_stat_statements has all the data since last reset of stats.
>
> So if you never reset stats, it accumulated data for howeve rlong you
> are using pg.
>
> Not really surprising that top total-time uses are the same.
>
> If you want to sensibly use it you should call reset function every now
> and then.
Resetting the data adds some noticeable overhead as newly added entries will
need to generate a normalize query string and so on. What most people do is
taking regular snapshots of pg_stat_statements (and other stats) view and then
compare the snapshots. There are a few open source projects doing that
available.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Exact same output - pg_stat_statements
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
2023-01-02 13:34 ` Re: Exact same output - pg_stat_statements hubert depesz lubaczewski <depesz@depesz.com>
2023-01-03 05:52 ` Re: Exact same output - pg_stat_statements Julien Rouhaud <rjuju123@gmail.com>
@ 2023-01-09 10:15 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2023-01-09 10:15 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Rushikesh socha <rushikesh.s@gmail.com>; pgsql-general@lists.postgresql.org
On Tue, Jan 03, 2023 at 01:52:17PM +0800, Julien Rouhaud wrote:
> Resetting the data adds some noticeable overhead as newly added entries will
> need to generate a normalize query string and so on. What most people do is
> taking regular snapshots of pg_stat_statements (and other stats) view and then
> compare the snapshots. There are a few open source projects doing that
> available.
Why do you assume that the cost of normalization query string will be in
any way measurable? Especially in comparison do extra cost of doin math
on potentially thousands of rows?
Best regards,
depesz
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-09 10:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-30 16:04 Exact same output - pg_stat_statements Rushikesh socha <rushikesh.s@gmail.com>
2022-12-30 20:24 ` Ron <ronljohnsonjr@gmail.com>
2022-12-31 17:26 ` Rushikesh socha <rushikesh.s@gmail.com>
2023-01-02 13:34 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-01-03 05:52 ` Julien Rouhaud <rjuju123@gmail.com>
2023-01-09 10:15 ` hubert depesz lubaczewski <depesz@depesz.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