PostgreSQLスキルアップノート（自己啓発のための個人サイト）
実行計画を制御・プランナメソッド設定編
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 実行計画を制御・プランナメソッド設定編
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
2013/02/18
2013/03/15
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
プランナメソッド設定により実行計画を制御する方法。
【マニュアル】
第18章サーバの設定− 問い合わせ計画−プランナメソッド設定
→●[マニュアル]
第18章サーバの設定− 問い合わせ計画−プランナコスト定数
→●[マニュアル]
第14章性能に関するヒント・明示的なJOIN句でプランナを制御する
→●[マニュアル]
■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
set enable_nestloop = off;
のように、セッションの中で、特定の実行計画を採用させない指定をして実行計画を制御する。
指定できるのは以下の通り（9.2）
・デフォルトはすべてonなので、禁止させたいときにoff指定する。
・△印はoffにしても完全な禁止にはならない。
スキャン系
-------------------------------------------------------------------------------
enable_seqscan △ シーケンシャル走査計画
enable_indexscan インデックス走査計画型
enable_bitmapscan ビットマップ走査計画型 ビットマップスキャン
enable_indexonlyscan インデックス走査計画型のみ※１ インデックスオンリースキャン
enable_tidscan TID走査計画型 TIDスキャン
結合系
-------------------------------------------------------------------------------
enable_nestloop △ 入れ子になったループ結合計画 ネステッドループ
enable_mergejoin マージ結合計画型
enable_hashjoin ハッシュ結合計画型
集約・ソートその他
-------------------------------------------------------------------------------
enable_hashagg ハッシュ集約計画型 ※１
enable_sort △ 明示的並び替え手順(ソート）
enable_material △ 具体化の使用
第18章サーバの設定− 問い合わせ計画−プランナメソッド設定
→●[マニュアル]
2013/03/20現在 9.2マニュアル誤記等
※１「インデックス走査計画型のみ」ではなく「インデックスのみ走査計画型」？
どちらにしても日本語の説明にしない方が分かりやすい。
※２ enable_hashaggの説明がenable_hashjoinと同じになってしまっている。
■２■ 準備
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 テーブルt1（20件）,t2(100万件)作成
┌────────────────────────────┐
DROP TABLE if exists t1,t2;
CREATE TABLE t1 (c1 int PRIMARY KEY,c2 int);
CREATE TABLE t2 (c1 int PRIMARY KEY,c2 timestamp,c3 int);
WITH RECURSIVE tmp(c1,c2) AS (
SELECT 1,floor(random()*20) UNION ALL
SELECT c1+1,floor(random()*20) FROM tmp WHERE c1 < 20
)INSERT INTO t1 SELECT * FROM tmp;
--
WITH RECURSIVE tmp(c1,c2,c3) AS (
SELECT 1, current_timestamp,floor(random()*1000000) UNION ALL
SELECT c1+1, c2 + '1 hours' ,floor(random()*1000000) FROM tmp WHERE c1 < 1000000
)INSERT INTO t2 SELECT * FROM tmp;
VACUUM ANALYZE t1;
VACUUM ANALYZE t2;
-- t1とt2の定義と件数を確認
\d t[12];
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;
--
-- EXPLAIN準備
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
└────────────────────────────┘
実行計画の確認は、以降では
:ep1 SQL文; あるいは
:ep2 SQL文;
のようにして確認することができる。
EXPLAIN (ANALYZE,VERBOSE,BUFFERS) などの記法は、バッファなどより詳細な情報を
取得する指定でカッコとともに使う。（9.0以降の比較的新しい構文）
■３■ プランナメソッド設定による実行計画の制御
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
改めて接続しなおした場合のみ以下を実行する。
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
１．プランナメソッド設定なしでそのまま実行
──────────────────────────────
【□】
┌────────────────────────────┐
:ep1 select * from t2 where c1<1000000;
└────────────────────────────┘
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on public.t2 (cost=0.00..18870.00 rows=1000000 width=16) (actual time=0.031..320.899 rows=999999 loops=1)
Output: c1, c2, c3
Filter: (t2.c1 < 1000000)
Rows Removed by Filter: 1
Buffers: shared hit=2874 read=3496
Total runtime: 520.082 ms
・・・・絞り込みが非常に緩いので、シーケンススキャンが採用された。
２．設定を入れて実行
──────────────────────────────
【□】 以下のようにスキャン系の設定のうち、インデックスのみをon
┌────────────────────────────┐
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
set enable_indexonlyscan = off;
:ep1 select * from t2 where c1<1000000;
└────────────────────────────┘
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using t2_pkey on public.t2 (cost=0.00..34853.36 rows=1000000 width=16) (actual time=0.055..396.595 rows=999999 loops=1)
Output: c1, c2, c3
Index Cond: (t2.c1 < 1000000)
Buffers: shared hit=1 read=9104
Total runtime: 600.702 ms
・・・・設定によってインデックススキャンに変わった。
以降未整備
以上
