Hi,
We have servers where there is single app db, but one that contains MANY
schema/tables.
This is on Pg 12.6.
Simple query like: select * from pg_stat_database where datname = 'app_name' can take up to 800ms!
#v+
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
Subquery Scan on d (cost=0.00..2.52 rows=2 width=216) (actual time=883.623..883.631 rows=1 loops=1)
-> Append (cost=0.00..2.39 rows=2 width=68) (actual time=0.019..0.025 rows=1 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..0.02 rows=1 width=68) (actual time=0.003..0.003 rows=0
loops=1)
-> Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.002..0.003 rows=0 loops=1)
One-Time Filter: NULL::boolean
-> Bitmap Heap Scan on pg_database (cost=1.24..2.35 rows=1 width=68) (actual time=0.016..0.020 rows=1
loops=1)
Recheck Cond: (datname = 'app_name'::name)
Heap Blocks: exact=1
-> Bitmap Index Scan on pg_database_datname_index (cost=0.00..1.24 rows=1 width=0) (actual
time=0.009..0.010rows=1 loops=1)
Index Cond: (datname = 'app_name'::name)
Planning Time: 0.294 ms
Execution Time: 883.684 ms
(12 rows)
#v-
I checked and it looks that the problem is with pg_stat_get_db_* functions that are used in this view. For example:
#v+
=# explain (analyze on, buffers on) SELECT pg_stat_get_db_temp_bytes(7436115) AS temp_bytes;
QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════════
Result (cost=0.00..0.01 rows=1 width=8) (actual time=465.152..465.153 rows=1 loops=1)
Planning Time: 0.017 ms
Execution Time: 465.175 ms
(3 rows)
#v-
Is there anything we could do to make it faster?
The problem is that on certain servers this query takes up to 10% of
total query time (as reported by pg_stat_statements).
This query is being called, quite a lot, by monitoring software, and
disabling it is not really an option. It is called every 15 seconds. So
not extremely often, but the total_time adds up "nicely".
Best regards,
depesz