Greetings,
I’ve found very large statements being kept in the pg_stat_statements view/function:
# select length(query) from pg_stat_statements;
length
---------
876153
879385
171
44
3796
873527
<snip>
896454
864538
1869286
938
869891
<snip>
883526
877365
(969 rows)
Time: 9898.411 ms (00:09.898)
As you can see, it takes a little time. Interestingly enough so does a count:
# select count(*) from pg_stat_statements;
count
-------
971
(1 row)
Time: 6457.985 ms (00:06.458)
Using showtext:=false shows the impact of the large columns:
# select count(*) from pg_stat_statements(showtext:=false);
count
-------
970
(1 row)
Time: 10.644 ms
I suppose 9 seconds not too long to wait, but when a monitoring package is in place and this query executes every 10 – 15 seconds, plus all the other queries, there is a real performance hit.
The obvious answer is use the second query, but what if you need the queryid?
- Is the queryid stored in just the pg_stat_statements?
- Why do the large queries stored in the pg_stat_statements make the query run long:
Here’s an explain:
# explain (analyze,buffers,verbose) select queryid, query from pg_stat_statements;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Function Scan on tsdba.pg_stat_statements (cost=0.00..10.00 rows=1000 width=40) (actual time=5031.202..7719.943 rows=986 loops=1)
Output: pg_stat_statements.queryid, pg_stat_statements.query
Function Call: pg_stat_statements(true)
Buffers: shared hit=1, temp read=89985 written=89985
Planning:
Buffers: shared hit=4
Planning Time: 7.194 ms
Execution Time: 7805.840 ms
(8 rows)
Time: 7832.797 ms (00:07.833)
Phil Godfrin | Database Administration
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M 281.825.2311
E Philippe.Godfrin@nov.com
