Hello community, As you know, pg_stat_statements is very popular and useful extension used by many Postgres user. What do you think of adding to store or capture also query plans as new feature in this extension? This would be an optional feature by default closed controling with a parameter maybe
On Tue, 3 Oct 2023 at 22:03, wrote:
> So one last question, should I expect the patch to land in version 17 only or
> is there chance that it will also be in lower versions right away?
It wouldn't ever be put into anything earlier than 17.
David
ts gets translated into this:
> > IN ($1, $2)
> > and so on."
> >
> > The questions are:
> > 1. Shouldnt IN behave so that the query in pg_stat_statements would look
> > like this:
> > IN $1
> > 2. Shouldnt there be at least some flag to aggr
On Tue, 2023-10-03 at 08:05 +, byme@byme.email wrote:
> "This obfuscates our monitoring because the same query with different amount
> of arguments gets translated into this:
> IN ($1, $2)
> and so on."
>
> The questions are:
> 1. Shouldnt IN behave so that
On Tue, 3 Oct 2023 at 21:07, wrote:
> P.S.: The only serious discussion I was able to find about it was from 2015
> here, everyone basically stating that the improvement would be useful.
> https://postgrespro.com/list/thread-id/1880012
There is some active discussion and a patch which aims to i
houldnt IN behave so that the query in pg_stat_statements would look like
this:
IN $1
2. Shouldnt there be at least some flag to aggregate such queries into one?
3. Is there any workaround how to aggregate those queries except the "= ANY"?
4. How come no one is bothered b
byme@byme.email schreef op ma 02-10-2023 om 16:19 [+]:
>
>
> Is there a possibility the pg_stat_statements will be improved with
> handling IN? This problem makes it so much less useful right now.
not sure what the question is,
but if you change pg_stat_statements with anothe
Hello,
I would like to ask about a problem that is bothering me for a while now. We
have implemented monitoring of our queries using pg_stat_statements. The only
problem we have with it is that expressions with
IN ('first', 'second', 'third')
get translated into
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)
el 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 r
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
> inf
> > vary dramatically and might need different tuning on each. So having
> > > separate rows in the pg_stat_statements output makes sense.
> >
> > Yes, having different rows seems like a good thing. But being unable to
> > tell
> > which row appl
eneficial feature.
> >
> > If the same SQL is executed against different sets of tables, each
> > with different indexes, probably different data, the performance could
> > vary dramatically and might need different tuning on each. So having
> > separate rows in the pg_
bles, each
> with different indexes, probably different data, the performance could
> vary dramatically and might need different tuning on each. So having
> separate rows in the pg_stat_statements output makes sense.
Yes, having different rows seems like a good thing. But being unable to tel
n app that uses multiple schemas.
> > It will do a set schema 'schema_name' and execute queries. The queries
> > executed are the same regardless of the schema the connection set.
> >
> > In pg_stat_statements the exact same query will get a different queryid for
>
nd execute queries. The queries
> executed are the same regardless of the schema the connection set.
>
> In pg_stat_statements the exact same query will get a different queryid for
> each schema that executes the query.
>
> I'm unable to determine which queryid comes from which
a the connection set.
In pg_stat_statements the exact same query will get a different queryid for
each schema that executes the query.
I'm unable to determine which queryid comes from which schema the query was
executed under. Is anyone aware of a way to determine this?
Thanks in advance.
e you'd want to traverse the
>>> plan
>>> tree.)
>>> I'm not convinced that it's practical for pg_stat_statements to make a
>>> new
>>> shared hashtable entry under those constraints. But figuring out how to
>>> minimize the ris
t;> and anything that might itself throw an error had best be avoided as
>> well. (Which, among other things, means that examining executor state
>> would be a bad idea, and I'm not even sure you'd want to traverse the plan
>> tree.)
>> I'm not convinced
ed here is for
> pgss_ExecutorRun (and maybe some other existing functions in
> pg_stat_statements) to go ahead and record the statement when they catch
> an error thrown out of standard_ExecutorRun, rather than just updating
> the module's nested_level variable and re-throwing
On 13 January 2020 20:15:21 CET, Rushikesh socha wrote:
>HI, Is there any good link that shows how to install pg_stat_statements
>extension
>I am getting below error
>
>postgres=# CREATE EXTENSION pg_stat_statements;
>ERROR: could not open extension control file
>"/us
On Mon, Jan 13, 2020 at 11:41:36AM -0800, Adrian Klaver wrote:
> How did the above get installed, from source, RPM. other?
Rushikesh, depending on your environment, the way to install
pg_stat_statements' libraries may change. On most Linux
distributions, any extension modules are shippe
cha wrote:
> HI, Is there any good link that shows how to
install pg_stat_statements
> extension
Postgres version?
How did you install Postgres?
> I am getting below error
>
> postgres=# CREATE EXTENSION pg_stat_statements;
> ERROR: could
.
Rushikesh Socha
Edison,NJ,USA
Cell : +1 860 794 4643
On Mon, Jan 13, 2020 at 2:24 PM Adrian Klaver
wrote:
> On 1/13/20 11:15 AM, Rushikesh socha wrote:
> > HI, Is there any good link that shows how to install pg_stat_statements
> > extension
>
> Postgres version?
>
> H
On 1/13/20 11:15 AM, Rushikesh socha wrote:
HI, Is there any good link that shows how to install pg_stat_statements
extension
Postgres version?
How did you install Postgres?
I am getting below error
postgres=# CREATE EXTENSION pg_stat_statements;
ERROR: could not open extension control
HI, Is there any good link that shows how to install pg_stat_statements
extension
I am getting below error
postgres=# CREATE EXTENSION pg_stat_statements;
ERROR: could not open extension control file
"/usr/pgsql-11/share/extension/pg_stat_statements.control": No such file or
directory
Thanks.
Does the pg_stat_statements.total_time include the time it takes for all
fetches of a cursor query. Or is it only the db time taken to execute the
query?
--
Regards,
Ayub
forgot to cc pgsql-general list
De : legrand legrand
Envoyé : vendredi 22 février 2019 20:26
À : Bruce Momjian
Objet : RE: pg_stat_statements doesn't track commit from pl/pgsql blocks
Hello Bruce,
thank you for taking time to answer.
yes, I was expe
$$;
> select calls,query from pg_stat_statements;
Uh, can you show me exactly what you were hoping to see? I see:
CREATE EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--
Hello,
It seems that pgss doesn't track commit (nor rollback) commands from
pl/pgsql blocks.
using psql in version 11.1:
select pg_stat_statements_reset();
do $$ begin commit; end $$;
select calls,query from pg_stat_statements;
...
I don't know how difficult it would be to fix it
I don't agree.
If we already had a column with the execution plan in the
pg_stat_statements then
it would be a workaround for the problem with the ambiguous names in the
query text column.
But we don't have such column, and I don't want to create a dependency
on the unimpleme
8-Nov-27, Sergei Agalakov wrote:
We do see that the queries are different but we can't see why they are so
much different in the execution time.
If the pg_stat_statements module would extend the object name to the
qualified names like s1.t1 and s2.t2 then we would see the report as
3004391
On 2018-Nov-27, legrand legrand wrote:
> There are also some tryies to extend pg_stat_statements
> with plans see
> https://www.postgresql-archive.org/FEATURE-PATCH-pg-stat-statements-with-plans-td5940964.html
Thread at
http://postgr.es/m/9e43fd8f-4d35-4b9d-545c-f9011cd4a...@uni-mu
On 2018-Nov-27, Sergei Agalakov wrote:
> We do see that the queries are different but we can't see why they are so
> much different in the execution time.
> If the pg_stat_statements module would extend the object name to the
> qualified names like s1.t1 and s2.t2 then we would
A part of the answer would be to store
explain (verbose on) select count(*) from t1;
result in pg_stat_statements for the corresponding query...
(Verbose On) gives the "qualified names":
QUERY PLAN
---
Aggregate (cost=19.38..19.39 rows=1 width=8)
Hi,
It would help to analyze performance issues if pg_stat_statements would
extend the object names to the qualified names.
Currently if we have two schemas ( say s1 and s2) with the objects with
the same name ( say tables t1) then
after the next executions:
set schema 's1';
se
of pgss.
This can help those working with waits sampling
as found in pg_stat_activity, pg_wait_sampling or pgsentinel.
note: some of the contentions are already reported as "LWLock" /
"pg_stat_statements"
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Hello,
What about adding a log message for each entry_dealloc() execution?
it could be usefull to start thinking increasing pg_stat_statements.max.
is there any rule regarding the acceptable max value ? I'm playing in test
with a 20 000 value without any problem, could it extendend to 100 000 ?
?
Knowing that there are also plans to add a planing counter in PG12, I would
suggest to add
plan_time,
exec_time,
pgss_time
the sum of those 3 counters being total_time.
This could help in investigating write contentions in pg_stat_statements
query file,
helping to define that max queries shou
rows
from pg_stat_statements
) stmts
group by rollup(bucket) order by bucket;
bucket | entries | max_calls | total_time | pct_time | rows | pct_rows
+-+---++--++--
1 | 245 | 71 | 4745479 | 0.38
Hi,
This is the expected behavior, like that sql statements with différent
values are shared in pg_stat_statements ...
There is no parameter to change this, if you really want query paramèters
you néed to have a look at statements logging, but be carrefull this can
generate a huge volume of log
Hello
I am using postgres 10.3 version current facing an issue related query
tracking whenever I run SELECT * FROM pg_stat_statements in the query
column I am not getting the exact query with values with variable but
instead I am getting $1, $2 in the query
example- "update ss set number=$1
Hello,
Here is a patch that :
- adds a new guc: pg_stat_statements.track_errors boolean (default to true),
- capture of DML, DDL, PL/PGSQL commands in error into pgss.
There is always a risk that new code used in PG_CATCH (mainly pgss_store)
gives an error.
I'm not able to tell when it could occ
Progress report on this subject:
1/ Some clarifications:
What is expected here is to update pgss counters for ALL the queries that
have been executed,
taking into account queries finished in SUCCESS and thoses finised with
ERROR.
Main interest here is to catch queries that are cancelled or inte
OK no need of a new hook.
I'll try to implement what you suggest here, but this clearly exceeds my
(poor) development skill.
As you also noticed, in addition to collect this SQL statement counters for
the corresponding QueryId,
I would have been interested in its PlanId ... but it's an other subj
unctions in
pg_stat_statements) to go ahead and record the statement when they catch
an error thrown out of standard_ExecutorRun, rather than just updating
the module's nested_level variable and re-throwing.
The hard part here is that you have to be really careful what you do in
a PG_CATCH block, because th
OK I see ...
This hook should be used only for ERROR (no WARNING nor NOTICE ...)
and the only real interesting information is
ErrorData -> internalquery; /* text of internally-generated query */
This doesn't permit to (re)build the link to queryid
(that is based on parse tree, but not availa
Hello,
On Thu, Apr 26, 2018 at 01:24:25PM -0700, legrand legrand wrote:
> Hello all,
>
> I was wondering if there is a hook to collect non successfully finished SQL
> statements in pg_stat_statements (timed-out, cancelled, killed, or simply
> errored) ?
Some time ago I looked
Hello all,
I was wondering if there is a hook to collect non successfully finished SQL
statements in pg_stat_statements (timed-out, cancelled, killed, or simply
errored) ?
Thanks in advance
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Sat, Mar 24, 2018 at 12:17:30PM +1300, David Rowley wrote:
> If it is, then it's not a bug in pg_stat_statements. log_statement =
> 'ddl' would have kept a record of the same thing.
>
> Perhaps the best fix would be a documentation improvement to mention
> the
On 24 March 2018 at 10:30, legrand legrand wrote:
> It seems that passwords used in commands are not removed when caught by
> pg_stat_statements
> (they are not "normalized" being utility statements)
>
> exemple:
> alter role tt with password '123';
>
>
Hello,
It seems that passwords used in commands are not removed when caught by
pg_stat_statements
(they are not "normalized" being utility statements)
exemple:
alter role tt with password '123';
select query from public.pg_stat_statements
where query like
52 matches
Mail list logo
