|
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?
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 |
