search

pgpool / pgpool2

This is the official mirror of git://git.postgresql.org/git/pgpool2.git. Note that this is just a *mirror* - we don't work with pull requests on github. Please subscribe to pgpool-hackers mailing list from our website and submit your patch to this mailing list.
https://www.pgpool.net
Other
305 stars 87 forks source link

pgpool is regularly doing table counts #20

Closed bart3r closed 5 years ago

bart3r commented 5 years ago

Our database server is continually get hit with queries like the following:

SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"designs"') AND c.relpersistence = 'u'

I assume this is coming from the pgpool software, as it is checking the status of tables? The problem is that some of these tables are huge and the query takes a long time, putting quite a lot of load on the server.

Is there a config setting to disable these table counts ?

pengbo0328 commented 5 years ago

Yes. Pgpool-II issues such queries to backend server. But Pgpool-II sends these queries only the first time when the table is accessed, because these query results are cached in pgpool child process local memory and subsequent queries use the cache.

But Pgpool-II child process doesn't share this cache, other connections will issue these queries again.

So far no ways to disable this feature.

bart3r commented 5 years ago

Why do you need to do a full row count ?

tatsuo-ishii commented 5 years ago

What's wrong with "full row count"? It scans a system catalog with index. Regardless it uses count(*) or not, it does not do a full scan on the table.

test=# explain analyze SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"designs"') AND c.relpersistence = 'u'; QUERY PLAN

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1) -> Index Scan using pg_class_oid_index on pg_class c (cost=0.28..8.29 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: (oid = (to_regclass('"designs"'::text))::oid) Filter: (relpersistence = 'u'::"char") Planning Time: 0.247 ms Execution Time: 0.123 ms (6 rows)