pg_stat_statements with large queries

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?

1. Is the queryid stored in just the pg_stat_statements?
2. 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(dot)Godfrin(at)nov(dot)com<mailto:Philippe(dot)Godfrin(at)nov(dot)com>