From 18df3f554741dcdec5b304c02d1542fb40e31ff8 Mon Sep 17 00:00:00 2001 From: Takashi Suzuki Date: Thu, 6 Dec 2012 17:04:02 +0900 Subject: [PATCH] =?utf8?q?CREATE=20TABLE=20AS=20EXECUTE=20=E3=81=AE?= =?utf8?q?=E8=A9=A6=E9=A8=93=E3=82=92=E3=80=90=E6=A9=9F=E8=83=BD=E3=81=AB?= =?utf8?q?=E4=BE=9D=E5=AD=98=E3=81=97=E3=81=AA=E3=81=84=E3=80=91=E8=A9=A6?= =?utf8?q?=E9=A8=93=E3=83=95=E3=82=A1=E3=82=A4=E3=83=AB=E5=86=85=E3=81=AB?= =?utf8?q?=E7=A7=BB=E5=8B=95=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Makefile | 4 +- expected/create_execute-9.1.out | 345 --------------------------------- expected/create_execute-9.2.out | 415 ---------------------------------------- expected/ut-A-9.1.out | 319 ++++++++++++++++++++++++++++++ expected/ut-A-9.2.out | 389 +++++++++++++++++++++++++++++++++++++ sql/create_execute.sql | 97 ---------- sql/ut-A.sql | 79 ++++++++ 7 files changed, 789 insertions(+), 859 deletions(-) delete mode 100644 expected/create_execute-9.1.out delete mode 100644 expected/create_execute-9.2.out delete mode 100644 sql/create_execute.sql diff --git a/Makefile b/Makefile index 61c4ab7..91228f2 100644 --- a/Makefile +++ b/Makefile @@ -5,9 +5,9 @@ # MODULES = pg_hint_plan -REGRESS = init base_plan pg_hint_plan fdw ut-init ut-A ut-S ut-J ut-L ut-G ut-fini create_execute +REGRESS = init base_plan pg_hint_plan fdw ut-init ut-A ut-S ut-J ut-L ut-G ut-fini -REGRESSION_EXPECTED = expected/init.out expected/base_plan.out expected/pg_hint_plan.out expected/ut-A.out expected/ut-S.out expected/ut-J.out expected/ut-L.out expected/ut-G.out expected/create_execute.out +REGRESSION_EXPECTED = expected/init.out expected/base_plan.out expected/pg_hint_plan.out expected/ut-A.out expected/ut-S.out expected/ut-J.out expected/ut-L.out expected/ut-G.out REGRESS_OPTS = --encoding=UTF8 diff --git a/expected/create_execute-9.1.out b/expected/create_execute-9.1.out deleted file mode 100644 index b7bf785..0000000 --- a/expected/create_execute-9.1.out +++ /dev/null @@ -1,345 +0,0 @@ -LOAD 'pg_hint_plan'; -SET pg_hint_plan.enable_hint TO on; -SET pg_hint_plan.debug_print TO on; -SET client_min_messages TO LOG; -SET search_path TO public; ----- ----- No. A-8-1 hint state output ----- -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -DEALLOCATE p1; --- No. A-8-1-9 --- No. A-8-1-10 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -DEALLOCATE p1; --- No. A-8-1-11 --- No. A-8-1-12 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -DEALLOCATE p1; --- No. A-8-1-13 --- No. A-8-1-14 -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -DEALLOCATE p1; diff --git a/expected/create_execute-9.2.out b/expected/create_execute-9.2.out deleted file mode 100644 index 4f44afa..0000000 --- a/expected/create_execute-9.2.out +++ /dev/null @@ -1,415 +0,0 @@ -LOAD 'pg_hint_plan'; -SET pg_hint_plan.enable_hint TO on; -SET pg_hint_plan.debug_print TO on; -SET client_min_messages TO LOG; -SET search_path TO public; ----- ----- No. A-8-1 hint state output ----- -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1); - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 < 1) -(2 rows) - -DEALLOCATE p1; --- No. A-8-1-9 --- No. A-8-1-10 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -DEALLOCATE p1; --- No. A-8-1-11 --- No. A-8-1-12 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -LOG: pg_hint_plan: -used hint: -SeqScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < 1000) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < 1000) -(4 rows) - -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -LOG: pg_hint_plan: -used hint: -BitmapScan(t1) -not used hint: -duplication hint: -error hint: - - QUERY PLAN ----------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 < $1) - -> Bitmap Index Scan on t1_i1 - Index Cond: (c1 < $1) -(4 rows) - -DEALLOCATE p1; --- No. A-8-1-13 --- No. A-8-1-14 -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; - QUERY PLAN ------------------------------- - Index Scan using t1_i1 on t1 - Index Cond: (c1 = 1) -(2 rows) - -DEALLOCATE p1; -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ------------------------ - Seq Scan on t1 - Filter: (c1 < 1000) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ------------------------ - Seq Scan on t1 - Filter: (c1 < 1000) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ------------------------ - Seq Scan on t1 - Filter: (c1 < 1000) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ------------------------ - Seq Scan on t1 - Filter: (c1 < 1000) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ------------------------ - Seq Scan on t1 - Filter: (c1 < 1000) -(2 rows) - -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 < $1) -(2 rows) - -DEALLOCATE p1; diff --git a/expected/ut-A-9.1.out b/expected/ut-A-9.1.out index f003be5..f4937ff 100644 --- a/expected/ut-A-9.1.out +++ b/expected/ut-A-9.1.out @@ -2597,6 +2597,325 @@ EXPLAIN (COSTS false) EXECUTE p1 (1000); (2 rows) DEALLOCATE p1; +-- No. A-8-1-9 +-- No. A-8-1-10 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +DEALLOCATE p1; +-- No. A-8-1-11 +-- No. A-8-1-12 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +DEALLOCATE p1; +-- No. A-8-1-13 +-- No. A-8-1-14 +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +DEALLOCATE p1; ---- ---- No. A-8-4 EXECUTE statement name error ---- diff --git a/expected/ut-A-9.2.out b/expected/ut-A-9.2.out index 7fd868c..59b7600 100644 --- a/expected/ut-A-9.2.out +++ b/expected/ut-A-9.2.out @@ -2667,6 +2667,395 @@ EXPLAIN (COSTS false) EXECUTE p1 (1000); (2 rows) DEALLOCATE p1; +-- No. A-8-1-9 +-- No. A-8-1-10 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +DEALLOCATE p1; +-- No. A-8-1-11 +-- No. A-8-1-12 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < 1000) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < 1000) +(4 rows) + +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 < $1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 < $1) +(4 rows) + +DEALLOCATE p1; +-- No. A-8-1-13 +-- No. A-8-1-14 +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +DEALLOCATE p1; +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +----------------------- + Seq Scan on t1 + Filter: (c1 < 1000) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +----------------------- + Seq Scan on t1 + Filter: (c1 < 1000) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +----------------------- + Seq Scan on t1 + Filter: (c1 < 1000) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +----------------------- + Seq Scan on t1 + Filter: (c1 < 1000) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +----------------------- + Seq Scan on t1 + Filter: (c1 < 1000) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 < $1) +(2 rows) + +DEALLOCATE p1; ---- ---- No. A-8-4 EXECUTE statement name error ---- diff --git a/sql/create_execute.sql b/sql/create_execute.sql deleted file mode 100644 index f039550..0000000 --- a/sql/create_execute.sql +++ /dev/null @@ -1,97 +0,0 @@ -LOAD 'pg_hint_plan'; -SET pg_hint_plan.enable_hint TO on; -SET pg_hint_plan.debug_print TO on; -SET client_min_messages TO LOG; -SET search_path TO public; - ----- ----- No. A-8-1 hint state output ----- - -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -DEALLOCATE p1; - -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1); -DEALLOCATE p1; - --- No. A-8-1-9 --- No. A-8-1-10 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -DEALLOCATE p1; - -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+SeqScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -DEALLOCATE p1; - --- No. A-8-1-11 --- No. A-8-1-12 -/*+SeqScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -DEALLOCATE p1; - -/*+BitmapScan(t1)*/ -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -DEALLOCATE p1; - --- No. A-8-1-13 --- No. A-8-1-14 -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; -DEALLOCATE p1; - -PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); -DEALLOCATE p1; - diff --git a/sql/ut-A.sql b/sql/ut-A.sql index 175d136..9348d2e 100644 --- a/sql/ut-A.sql +++ b/sql/ut-A.sql @@ -738,6 +738,85 @@ UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; EXPLAIN (COSTS false) EXECUTE p1 (1000); DEALLOCATE p1; +-- No. A-8-1-9 +-- No. A-8-1-10 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +DEALLOCATE p1; + +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +DEALLOCATE p1; + +-- No. A-8-1-11 +-- No. A-8-1-12 +/*+SeqScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +DEALLOCATE p1; + +/*+BitmapScan(t1)*/ +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +DEALLOCATE p1; + +-- No. A-8-1-13 +-- No. A-8-1-14 +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1; +DEALLOCATE p1; + +PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1'; +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000); +DEALLOCATE p1; + ---- ---- No. A-8-4 EXECUTE statement name error ---- -- 2.11.0