alter system flush shared_pool;
alter session set TRACEFILE_IDENTIFIER = 'trace2';
alter session set events '10053 trace name context forever, level 1';
select * from tab1;
alter session set events '10053 trace name context off';
SET optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size = 1048576;
select 1;
select * from information_schema.optimizer_trace\G
SET optimizer_trace="enabled=off";
https://tech-lab.sios.jp/archives/8865
--auto_explain
★ create extensionは不要
vim postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 100
auto_explain.log_analyze = on
auto_explain.log_buffers = on
CREATE TABLE hoge (a int);
CREATE TABLE fuga (a int);
INSERT INTO hoge VALUES (1);
INSERT INTO fuga VALUES (generate_series(1,1000000));
EXPLAIN ANALYZE VERBOSE SELECT * FROM hoge;
EXPLAIN ANALYZE VERBOSE SELECT * FROM fuga;
SELECT * FROM hoge;
SELECT * FROM fuga;
http://www.benjaminnevarez.com/2012/04/inside-the-query-optimizer-memo-structure/
(2014)
DBCC TRACEON(3604)
go
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (QUERYTRACEON 8608)
go
select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (QUERYTRACEON 8615)
go
