SQLチューニングアドバイザ
https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_sqltune.htm#GUID-8E1A39CB-A491-4254-8B31-9B1DF7B52AA1
調べたかぎり、見つからない
dexter
https://dev.classmethod.jp/server-side/db/guess-postgresql-index-with-dexter/
--必要なパッケージのインストール
wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
rpm -ivh pgdg-centos96-9.6-3.noarch.rpm
yum install -y postgresql96-server postgresql96-devel postgresql96-contrib
yum install -y gcc ruby ruby-devel
visudoでpostgresへsudo権限付与
echo postgres | passwd --stdin postgres
--pgのセットアップ
su - postgres
/usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data/
vim /var/lib/pgsql/9.6/data/postgresql.conf
log_min_duration_statement = 10
sudo systemctl restart postgresql-9.6
--dexterをインストール
export PATH=$PATH:/usr/pgsql-9.6/bin/
wget https://github.com/dalibo/hypopg/archive/1.0.0.tar.gz
tar xf 1.0.0.tar.gz
cd hypopg-1.0.0
make
su
make install
exit
gem install pgdexter
--動作確認
psql
create database test;
\c test
CREATE EXTENSION hypopg;
create table tab1 ( col1 int );
with generator as
( select a.*
from generate_series ( 1, 5000000 ) a
order by random()
)
insert into tab1 ( col1 )
select a
from generator;
analyze tab1;
create role user1 with login encrypted password 'user1';
grant all on database test to user1;
grant all on schema public to user1;
grant all on all tables in schema public to user1;
/var/lib/pgsql/bin/dexter postgresql://user1:user1@localhost/test -s "select col1 from tab1 where col1 = 1;"
データベース エンジン チューニング アドバイザー
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-2017
