А можете показать EXPLAIN (ANALYZE,BUFFERS) для обоих серверов?

Question

pgsql – PostgreSQL

Daniel

А можете показать EXPLAIN (ANALYZE,BUFFERS) для обоих серверов?

#backend #devops #pgsql #programming #russian

0

10.04.2020

2 ответов

6 просмотров

Andrii

новый сервер postgres=# EXPLAIN (ANALYZE,BUFFERS) SELECT psd.datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, 2 ^ 31 - age(datfrozenxid) as wraparound, deadlocks, temp_bytes, temp_files, pg_database_size(psd.datname) as pg_database_size FROM pg_stat_database psd JOIN pg_database pd ON psd.datname = pd.datname WHERE psd.datname not ilike 'template%%%%' AND psd.datname not ilike 'rdsadmin' AND psd.datname not ilike 'azure_maintenance' AND psd.datname not ilike 'postgres'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=0.24..0.82 rows=6 width=180) (actual time=10149.226..10151.443 rows=3 loops=1) Hash Cond: (d.datname = pd.datname) Buffers: shared hit=11 -> Seq Scan on pg_database d (cost=0.00..0.22 rows=6 width=68) (actual time=0.099..0.131 rows=3 loops=1) Filter: ((datname !~~* 'template%%%%'::text) AND (datname !~~* 'rdsadmin'::text) AND (datname !~~* 'azure_maintenance'::text) AND (datname !~~* 'postgres'::text)) Rows Removed by Filter: 3 Buffers: shared hit=1 -> Hash (cost=0.16..0.16 rows=6 width=68) (actual time=0.021..0.021 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on pg_database pd (cost=0.00..0.16 rows=6 width=68) (actual time=0.008..0.014 rows=6 loops=1) Buffers: shared hit=1 Planning time: 1.439 ms Execution time: 10151.563 ms (14 rows)

0

10.04.2020

Andrii · Accepted Answer

да, конечно старый сервер postgres=# EXPLAIN (ANALYZE,BUFFERS) SELECT psd.datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, 2 ^ 31 - age(datfrozenxid) as wraparound, deadlocks, temp_bytes, temp_files, pg_database_size(psd.datname) as pg_database_size FROM pg_stat_database psd JOIN pg_database pd ON psd.datname = pd.datname WHERE psd.datname not ilike 'template%%%%' AND psd.datname not ilike 'rdsadmin' AND psd.datname not ilike 'azure_maintenance' AND psd.datname not ilike 'postgres'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=0.24..0.82 rows=6 width=180) (actual time=15.078..16.882 rows=3 loops=1) Hash Cond: (d.datname = pd.datname) Buffers: shared hit=11 -> Seq Scan on pg_database d (cost=0.00..0.22 rows=6 width=68) (actual time=0.090..0.116 rows=3 loops=1) Filter: ((datname !~~* 'template%%%%'::text) AND (datname !~~* 'rdsadmin'::text) AND (datname !~~* 'azure_maintenance'::text) AND (datname !~~* 'postgres'::text)) Rows Removed by Filter: 3 Buffers: shared hit=1 -> Hash (cost=0.16..0.16 rows=6 width=68) (actual time=0.013..0.013 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on pg_database pd (cost=0.00..0.16 rows=6 width=68) (actual time=0.002..0.005 rows=6 loops=1) Buffers: shared hit=1 Planning time: 1.021 ms Execution time: 16.983 ms (14 rows)

172 похожих чатов

А можете показать EXPLAIN (ANALYZE,BUFFERS) для обоих серверов?

2 ответов

Похожие вопросы