Обсуждение: [PERFORM] pg_stat_statements with fetch
I'm spoiled by using pg_stat_statements to find the hotspot queries which could use some attention.
But with some recent work, all of the hotspots are of the form "FETCH 1000 FROM c3". The vast majority of the queries return less than 1000 rows, so only one fetch is issued per execution.
Is there an automated way to trace these back to the parent query, without having to strong-arm the driving application into changing its cursor-using ways?
pg_stat_statements v1.4 and postgresql v9.6 (or 10beta1, if it makes a difference)
Sometimes you can catch the DECLARE also being in pg_stat_statements, but it is not a sure thing and there is some risk the name got freed and reused.
log_min_duration_statement has the same issue.
Cheers,
Jeff
Would turning on logging of temp files help? That often reports the query that is using the temp files:
log_temp_files = 0
It probably wouldn't help if the cursor query never pulls from a temp file, but if it does ...
On Fri, May 19, 2017 at 7:04 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
I'm spoiled by using pg_stat_statements to find the hotspot queries which could use some attention.But with some recent work, all of the hotspots are of the form "FETCH 1000 FROM c3". The vast majority of the queries return less than 1000 rows, so only one fetch is issued per execution.Is there an automated way to trace these back to the parent query, without having to strong-arm the driving application into changing its cursor-using ways?pg_stat_statements v1.4 and postgresql v9.6 (or 10beta1, if it makes a difference)Sometimes you can catch the DECLARE also being in pg_stat_statements, but it is not a sure thing and there is some risk the name got freed and reused.log_min_duration_statement has the same issue.Cheers,Jeff