From e9fb3b1761ada540ddb0414535424a844bf68083 Mon Sep 17 00:00:00 2001 From: Mitsuru Hasegawa Date: Wed, 18 Apr 2012 19:36:29 +0900 Subject: [PATCH] =?utf8?q?PREPARE=E6=96=87=E3=81=AE=E3=83=AA=E3=82=B0?= =?utf8?q?=E3=83=AC=E3=83=83=E3=82=B7=E3=83=A7=E3=83=B3=E3=83=86=E3=82=B9?= =?utf8?q?=E3=83=88=E3=82=92=E8=BF=BD=E5=8A=A0=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Makefile | 8 +- expected/prepare-9.1.out | 614 +++++++++++++++++++++++++++++++++++++++ expected/prepare-9.2.out | 726 +++++++++++++++++++++++++++++++++++++++++++++++ sql/prepare.sql | 123 ++++++++ 4 files changed, 1468 insertions(+), 3 deletions(-) create mode 100644 expected/prepare-9.1.out create mode 100644 expected/prepare-9.2.out create mode 100644 sql/prepare.sql diff --git a/Makefile b/Makefile index a394e0f..cd512f9 100644 --- a/Makefile +++ b/Makefile @@ -5,7 +5,7 @@ # MODULES = pg_hint_plan -REGRESS = init base_plan pg_hint_plan +REGRESS = init base_plan pg_hint_plan prepare PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) @@ -13,10 +13,12 @@ include $(PGXS) expected/base_plan.out: expected/base_plan-$(MAJORVERSION).out cp expected/base_plan-$(MAJORVERSION).out expected/base_plan.out +expected/prepare.out: expected/prepare-$(MAJORVERSION).out + cp expected/prepare-$(MAJORVERSION).out expected/prepare.out .PHONY: subclean clean: subclean subclean: - rm -f expected/base_plan.out + rm -f expected/base_plan.out expected/prepare.out -installcheck: expected/base_plan.out +installcheck: expected/base_plan.out expected/prepare.out diff --git a/expected/prepare-9.1.out b/expected/prepare-9.1.out new file mode 100644 index 0000000..e30f04e --- /dev/null +++ b/expected/prepare-9.1.out @@ -0,0 +1,614 @@ +LOAD 'pg_hint_plan'; +SET pg_hint_plan.enable TO on; +SET pg_hint_plan.debug_print TO true; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 +(5 rows) + +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Scan using t2_pkey on t2 +(6 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:PREPAREでヒント句を指定しても、実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p1 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +INFO: /* +NestLoop(t1 t2) +*/ +EXPLAIN (COSTS false) EXECUTE p1; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: (id = t2.id) +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:パラメータがない場合は、1回目のEXPLAINで実行計画が決定する +/* NestLoop(t1 t2) */ +PREPARE p2 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +INFO: /* +NestLoop(t1 t2) +*/ +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p2; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: (id = t2.id) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p2; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: (id = t2.id) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p2; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: (id = t2.id) +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:5回目のEXPLAINまでヒント句を指定しても、6回目以降は本来の実行計画に戻る +/* NestLoop(t1 t2) */ +PREPARE p3 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +INFO: /* +NestLoop(t1 t2) +*/ +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINまでヒント句を指定すると、7回目以降も実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p4 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +INFO: /* +NestLoop(t1 t2) +*/ +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINでヒント句を指定すると、7回目以降も実行計画を制御できる +/* NestLoop(t1 t2) */ +PREPARE p5 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +INFO: /* +NestLoop(t1 t2) +*/ +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:7回目以降のEXPLAINでヒント句を指定しても、以降も実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p6 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +INFO: /* +NestLoop(t1 t2) +*/ +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +-- 9.1:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +-- 9.2:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p7 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +INFO: /* +NestLoop(t1 t2) +*/ +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 + Index Cond: ((id > $1) AND (id = t2.id)) +(5 rows) + +TRUNCATE t1; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Scan using t2_pkey on t2 +(6 rows) + +INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +-------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* NestLoop(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +-------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + diff --git a/expected/prepare-9.2.out b/expected/prepare-9.2.out new file mode 100644 index 0000000..813e790 --- /dev/null +++ b/expected/prepare-9.2.out @@ -0,0 +1,726 @@ +LOAD 'pg_hint_plan'; +SET pg_hint_plan.enable TO on; +SET pg_hint_plan.debug_print TO true; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + -> Index Only Scan using t2_pkey on t2 +(5 rows) + +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:PREPAREでヒント句を指定しても、実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p1 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) EXECUTE p1; + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + -> Index Only Scan using t2_pkey on t2 +(5 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:パラメータがない場合は、1回目のEXPLAINで実行計画が決定する +/* NestLoop(t1 t2) */ +PREPARE p2 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p2; +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p2; + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p2; + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 +(6 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:5回目のEXPLAINまでヒント句を指定しても、6回目以降は本来の実行計画に戻る +/* NestLoop(t1 t2) */ +PREPARE p3 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p3 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINまでヒント句を指定すると、7回目以降も実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p4 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +EXPLAIN (COSTS false) EXECUTE p4 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINでヒント句を指定すると、7回目以降も実行計画を制御できる +/* NestLoop(t1 t2) */ +PREPARE p5 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p5 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); +INFO: /* +HashJoin(t1 t2) +*/ +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:7回目以降のEXPLAINでヒント句を指定しても、以降も実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p6 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +-- 9.1:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +-- 9.2:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p7 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + Filter: (id > 10) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +TRUNCATE t1; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + QUERY PLAN +------------------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > 10) + -> Index Only Scan using t2_pkey on t2 +(6 rows) + +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +INFO: /* +HashJoin(t1 t2) +*/ + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + +/* NestLoop(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.id = t2.id) + -> Index Only Scan using t1_pkey on t1 + Index Cond: (id > $1) + -> Hash + -> Seq Scan on t2 +(7 rows) + diff --git a/sql/prepare.sql b/sql/prepare.sql new file mode 100644 index 0000000..89ed91f --- /dev/null +++ b/sql/prepare.sql @@ -0,0 +1,123 @@ +LOAD 'pg_hint_plan'; +SET pg_hint_plan.enable TO on; +SET pg_hint_plan.debug_print TO true; + +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:PREPAREでヒント句を指定しても、実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p1 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) EXECUTE p1; + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:パラメータがない場合は、1回目のEXPLAINで実行計画が決定する +/* NestLoop(t1 t2) */ +PREPARE p2 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p2; +EXPLAIN (COSTS false) EXECUTE p2; +EXPLAIN (COSTS false) EXECUTE p2; + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:5回目のEXPLAINまでヒント句を指定しても、6回目以降は本来の実行計画に戻る +/* NestLoop(t1 t2) */ +PREPARE p3 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p3 (10); +EXPLAIN (COSTS false) EXECUTE p3 (10); +EXPLAIN (COSTS false) EXECUTE p3 (10); +EXPLAIN (COSTS false) EXECUTE p3 (10); + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINまでヒント句を指定すると、7回目以降も実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p4 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p4 (10); +EXPLAIN (COSTS false) EXECUTE p4 (10); +EXPLAIN (COSTS false) EXECUTE p4 (10); + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:6回目のEXPLAINでヒント句を指定すると、7回目以降も実行計画を制御できる +/* NestLoop(t1 t2) */ +PREPARE p5 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +EXPLAIN (COSTS false) EXECUTE p5 (10); +EXPLAIN (COSTS false) EXECUTE p5 (10); +EXPLAIN (COSTS false) EXECUTE p5 (10); +EXPLAIN (COSTS false) EXECUTE p5 (10); +EXPLAIN (COSTS false) EXECUTE p5 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p5 (10); + +-- 9.1:PREPAREで指定したヒント句で実行計画が固定される +-- 9.2:7回目以降のEXPLAINでヒント句を指定しても、以降も実行計画は制御できない +/* NestLoop(t1 t2) */ +PREPARE p6 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +EXPLAIN (COSTS false) EXECUTE p6 (10); +EXPLAIN (COSTS false) EXECUTE p6 (10); +EXPLAIN (COSTS false) EXECUTE p6 (10); +EXPLAIN (COSTS false) EXECUTE p6 (10); +EXPLAIN (COSTS false) EXECUTE p6 (10); +EXPLAIN (COSTS false) EXECUTE p6 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p6 (10); + +-- 9.1:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +-- 9.2:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される +/* NestLoop(t1 t2) */ +PREPARE p7 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +EXPLAIN (COSTS false) EXECUTE p7 (10); +EXPLAIN (COSTS false) EXECUTE p7 (10); + +TRUNCATE t1; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); + +INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; +ANALYZE t1; +EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10; +/* HashJoin(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); +/* NestLoop(t1 t2) */ +EXPLAIN (COSTS false) EXECUTE p7 (10); -- 2.11.0