А можете на новом сделать set enable_hashjoin=off; и ещё

Question

pgsql – PostgreSQL

Daniel

А можете на новом сделать set enable_hashjoin=off; и ещё

разочек explain?

#backend #devops #pgsql #programming #russian

0

10.04.2020

1 ответов

8 просмотров

Andrii · Accepted Answer

postgres=# show enable_hashjoin; enable_hashjoin ----------------- off (1 row) 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=0.54..0.94 rows=6 width=180) (actual time=10150.532..10153.274 rows=3 loops=1) Merge Cond: (d.datname = pd.datname) Buffers: shared hit=8 -> Sort (cost=0.30..0.31 rows=6 width=68) (actual time=0.078..0.080 rows=3 loops=1) Sort Key: d.datname Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pg_database d (cost=0.00..0.22 rows=6 width=68) (actual time=0.039..0.060 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 -> Sort (cost=0.24..0.25 rows=6 width=68) (actual time=0.019..0.023 rows=5 loops=1) Sort Key: pd.datname Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pg_database pd (cost=0.00..0.16 rows=6 width=68) (actual time=0.004..0.007 rows=6 loops=1) Buffers: shared hit=1 Planning time: 0.492 ms Execution time: 10153.343 ms (19 rows)

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

А можете на новом сделать set enable_hashjoin=off; и ещё

1 ответов

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