select /*+ USE_NL(A B) */
A.*
from tab1 A
inner join tab1 B
on A.col1 = B.col1
;
select /*+ USE_MERGE(A B) */
A.*
from tab1 A
inner join tab1 B
on A.col1 = B.col1
;
select /*+ USE_HASH(A B) */
A.*
from tab1 A
inner join tab1 B
on A.col1 = B.col1
;
SELECT * FROM tab6 USE INDEX (ind61)
WHERE col1=1 AND col2='2' AND col3=current_date;
SELECT * FROM tab6 IGNORE INDEX (ind62)
WHERE col1=1 AND col2='2' AND col3=current_date;
SELECT * FROM tab6 FORCE INDEX (ind62,PRIMARY )
WHERE col1=1 AND col2='2' AND col3=current_date;
explain select straight_join A.* from tab1 A inner join tab2 B on A.col1 = B.col1;
explain select straight_join A.* from tab1 B inner join tab2 A on A.col1 = B.col1;
explain select A.* from tab1 A straight_join tab2 B on A.col1 = B.col1;
explain select A.* from tab1 B straight_join tab2 A on A.col1 = B.col1;
pg_hint_plan
https://www.sraoss.co.jp/technology/postgresql/3rdparty/pg_hint_plan.php
wget https://ja.osdn.net/projects/pghintplan/downloads/70273/pg_hint_plan94-1.1.7-1.el7.x86_64.rpm
rpm -ivh pg_hint_plan94-1.1.7-1.el7.x86_64.rpm
vim postgresql.conf
shared_preload_libraries = 'pg_hint_plan'
sudo systemctl restart postgresql-9.4
CREATE EXTENSION pg_hint_plan;
/usr/pgsql-9.4/bin/pgbench -U postgres -i -s 10 test
VACUUM ANALYZE;
EXPLAIN ANALYZE
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
/*+ HashJoin(a b) SeqScan(a) */
EXPLAIN ANALYZE
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
※ヒント句で指定するテーブル名は小文字で指定する必要がある
※テーブル別名使用の場合はヒント句でも別名を使用する必要がある
https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
クエリヒント
SELECT *
FROM dbo.tab1 AS c
INNER JOIN dbo.tab2 AS d ON c.col1 = d.col1
WHERE c.col1 = 5
OPTION (MERGE JOIN);
GO
SELECT *
FROM dbo.tab1 AS c
INNER JOIN dbo.tab2 AS d ON c.col1 = d.col1
WHERE c.col1 = 5
OPTION (HASH JOIN);
GO
SELECT *
FROM dbo.tab1 AS c
INNER JOIN dbo.tab2 AS d ON c.col1 = d.col1
WHERE c.col1 = 5
OPTION (LOOP JOIN);
GO
SELECT *
FROM dbo.tab1 AS c
WITH (INDEX(ind11))
INNER JOIN dbo.tab2 AS d
ON c.col1 = d.col1
WHERE c.col1 = 5
OPTION (LOOP JOIN)
GO
SELECT MAX(col1)
FROM dbo.tab1
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
GO
