From a6777ff555e648faa746f9d45706b9047b3751c6 Mon Sep 17 00:00:00 2001 From: Mitsuru Hasegawa Date: Mon, 6 Aug 2012 20:13:01 +0900 Subject: [PATCH] =?utf8?q?8=E6=9C=886=E6=97=A5=E6=B6=88=E5=8C=96=E5=88=86?= =?utf8?q?=E3=81=AE=E8=A9=A6=E9=A8=93=E3=82=92=E8=BF=BD=E5=8A=A0=E3=81=97?= =?utf8?q?=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Makefile | 2 +- expected/ut-S.out | 993 +++++++++++++++++++++++++++++++++++++++++++++++++++ expected/ut_init.out | 9 +- sql/ut_init.sql | 9 +- 4 files changed, 1008 insertions(+), 5 deletions(-) create mode 100644 expected/ut-S.out diff --git a/Makefile b/Makefile index 65c198d..3bae761 100644 --- a/Makefile +++ b/Makefile @@ -5,7 +5,7 @@ # MODULES = pg_hint_plan -REGRESS = init base_plan pg_hint_plan prepare fdw ut_init ut-A ut_fini +REGRESS = init base_plan pg_hint_plan prepare fdw ut_init ut-A ut-S ut_fini EXTRA_CLEAN = sql/fdw.sql expected/base_plan.out expected/prepare.out expected/fdw.out diff --git a/expected/ut-S.out b/expected/ut-S.out new file mode 100644 index 0000000..a2b6804 --- /dev/null +++ b/expected/ut-S.out @@ -0,0 +1,993 @@ +LOAD 'pg_hint_plan'; +SET pg_hint_plan.enable TO on; +SET pg_hint_plan.debug_print TO on; +SET client_min_messages TO LOG; +SET search_path TO public; +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +---- +---- No. S-1-1 specified pattern of the object name +---- +-- No. S-1-1-1 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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) + +-- No. S-1-1-2 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(t1) +duplication hint: +error hint: + + QUERY PLAN +------------------------------------ + Index Scan using t1_pkey on t1 t_1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-1-1-3 +/*+SeqScan(t_1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t_1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 t_1 + Filter: (c1 = 1) +(2 rows) + +---- +---- No. S-1-2 specified schema name in the hint option +---- +-- No. S-1-2-1 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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) + +-- No. S-1-2-2 +/*+SeqScan(s1.t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(s1.t1) +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +---- +---- No. S-1-3 table doesn't exist in the hint option +---- +-- No. S-1-3-1 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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) + +-- No. S-1-3-2 +/*+SeqScan(t2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(t2) +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +---- +---- No. S-1-4 conflict table name +---- +-- No. S-1-4-1 +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1; + QUERY PLAN +-------------------------------------- + Nested Loop + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 + Filter: (c1 = 1) +(5 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------- + Nested Loop + -> Seq Scan on t1 + Filter: (c1 = 1) + -> Seq Scan on t2 + Filter: (c1 = 1) +(5 rows) + +-- No. S-1-4-2 +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1; + QUERY PLAN +-------------------------------------- + Nested Loop + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> Seq Scan on t1 + Filter: (c1 = 1) +(5 rows) + +/*+IndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1; +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------- + Nested Loop + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(5 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------- + Nested Loop + -> Seq Scan on t1 + Filter: (c1 = 1) + -> Seq Scan on t1 s2t1 + Filter: (c1 = 1) +(5 rows) + +/*+BitmapScan(s2t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1; +LOG: pg_hint_plan: +used hint: +BitmapScan(s2t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Nested Loop + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> Bitmap Heap Scan on t1 s2t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = 1) +(7 rows) + +-- No. S-1-4-3 +EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1; + QUERY PLAN +--------------------------------------------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(8 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------------------- + Seq Scan on t1 + Filter: (c1 = 1) + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Seq Scan on t1 + Filter: ((c1 IS NOT NULL) AND (c1 = 1)) +(8 rows) + +/*+SeqScan(t11)*/ +EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t11) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------------------- + Index Scan using t1_pkey on t1 t12 + Index Cond: (c1 = 1) + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Seq Scan on t1 t11 + Filter: ((c1 IS NOT NULL) AND (c1 = 1)) +(8 rows) + +/*+SeqScan(t12)*/ +EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t12) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------- + Seq Scan on t1 t12 + Filter: (c1 = 1) + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 t11 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(8 rows) + +---- +---- No. S-1-5 object type for the hint +---- +-- No. S-1-5-1 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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) + +-- No. S-1-5-2 +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; + QUERY PLAN +--------------------------------- + Result + -> Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(6 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------- + Result + -> Append + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1 + Index Cond: (c1 = 1) +(6 rows) + +-- No. S-1-5-3 +EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; + QUERY PLAN +---------------------------------- + Index Scan using ul1_pkey on ul1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(ul1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(ul1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ul1 + Filter: (c1 = 1) +(2 rows) + +-- No. S-1-5-4 +CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tm1_pkey" for table "tm1" +EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1; + QUERY PLAN +---------------------------------- + Index Scan using tm1_pkey on tm1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(tm1)*/ +EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(tm1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on tm1 + Filter: (c1 = 1) +(2 rows) + +-- No. S-1-5-5 +EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; + QUERY PLAN +------------------------------------------------- + Index Scan using pg_class_oid_index on pg_class + Index Cond: (oid = 1::oid) +(2 rows) + +/*+SeqScan(pg_class)*/ +EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(pg_class) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------- + Seq Scan on pg_class + Filter: (oid = 1::oid) +(2 rows) + +-- No. S-1-5-6 +-- refer fdw.sql +-- No. S-1-5-7 +EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1; + QUERY PLAN +------------------------- + Function Scan on f1 ft1 + Filter: (c1 = 1) +(2 rows) + +/*+SeqScan(ft1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(ft1) +duplication hint: +error hint: + + QUERY PLAN +------------------------- + Function Scan on f1 ft1 + Filter: (c1 = 1) +(2 rows) + +-- No. S-1-5-8 +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; + QUERY PLAN +--------------------------- + Values Scan on "*VALUES*" + Filter: (column1 = 1) +(2 rows) + +/*+SeqScan(val1)*/ +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(val1) +duplication hint: +error hint: + + QUERY PLAN +--------------------------- + Values Scan on "*VALUES*" + Filter: (column1 = 1) +(2 rows) + +/*+SeqScan(*VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(*VALUES*) +duplication hint: +error hint: + + QUERY PLAN +--------------------------- + Values Scan on "*VALUES*" + Filter: (column1 = 1) +(2 rows) + +-- No. S-1-5-9 +EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) +SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1; + QUERY PLAN +--------------------------------------------------------------------- + Nested Loop + CTE c1 + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> CTE Scan on c1 + Filter: (c1 = 1) +(11 rows) + +/*+SeqScan(c1)*/ +EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) +SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(c1) +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------- + Nested Loop + CTE c1 + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + -> CTE Scan on c1 + Filter: (c1 = 1) +(11 rows) + +-- No. S-1-5-10 +EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; + QUERY PLAN +------------------------------------- + Index Scan using t1_pkey on t1 v1t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(v1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(v1) +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Index Scan using t1_pkey on t1 v1t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-1-5-11 +EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(s1)*/ +EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(s1) +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +---- +---- No. S-3-1 scan method hint +---- +-- No. S-3-1-1 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +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) + +-- No. S-3-1-2 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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) + +-- No. S-3-1-3 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+IndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-4 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-1-5 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +-- No. S-3-1-6 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +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_pkey + Index Cond: (c1 = 1) +(4 rows) + +-- No. S-3-1-7 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +/*+TidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +TidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-1-8 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); + QUERY PLAN +------------------------------------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + Filter: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) +(3 rows) + +/*+TidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); +LOG: pg_hint_plan: +used hint: +TidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------- + Tid Scan on t1 + TID Cond: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-1-9 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+NoSeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +NoSeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-1-10 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoSeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoSeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-11 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoIndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoIndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------ + Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = 1) +(4 rows) + +-- No. S-3-1-12 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+NoIndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +NoIndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +-- No. S-3-1-13 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +/*+NoBitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; +LOG: pg_hint_plan: +used hint: +NoBitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c3 < 10) +(2 rows) + +-- No. S-3-1-14 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoBitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoBitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-15 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +/*+NoTidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +NoTidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-1-16 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoTidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoTidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using t1_pkey on t1 + Index Cond: (c1 = 1) +(2 rows) + diff --git a/expected/ut_init.out b/expected/ut_init.out index 4168a9d..1107d1f 100644 --- a/expected/ut_init.out +++ b/expected/ut_init.out @@ -131,7 +131,9 @@ CREATE TABLE s1.r2 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "r2_pkey" for table "r2" CREATE TABLE s1.r3 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "r3_pkey" for table "r3" -INSERT INTO s1.t1 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 1000) i) t; +CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ul1_pkey" for table "ul1" +INSERT INTO s1.t1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; INSERT INTO s1.t2 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.t3 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.t4 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; @@ -154,7 +156,7 @@ INSERT INTO s1.p2c3c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(251, 3 INSERT INTO s1.r1 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.r2 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.r3 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; -CREATE INDEX t1_i ON s1.t1 (c2); +CREATE INDEX t1_i ON s1.t1 (c3); CREATE INDEX t2_i ON s1.t2 (c2); CREATE INDEX t3_i ON s1.t3 (c2); CREATE INDEX t4_i ON s1.t4 (c2); @@ -186,4 +188,7 @@ CREATE VIEW s1.v1 AS SELECT v1t1.c1, v1t1.c2, v1t1.c3, v1t1.c4 FROM s1.t1 v1t1; CREATE VIEW s1.v2 AS SELECT v2t1.c1, v2t1.c2, v2t1.c3, v2t1.c4 FROM s1.t1 v2t1 JOIN s1.t2 v2t2 ON(v2t1.c1 = v2t2.c1); CREATE VIEW s1.v3 AS SELECT v3t1.c1, v3t1.c2, v3t1.c3, v3t1.c4 FROM s1.t1 v3t1 JOIN s1.t2 v3t2 ON(v3t1.c1 = v3t2.c1) JOIN s1.t3 v3t3 ON(v3t1.c1 = v3t3.c1); ANALYZE; +CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$ +VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3') +$$ LANGUAGE sql; \q diff --git a/sql/ut_init.sql b/sql/ut_init.sql index 4a92c4d..a469533 100644 --- a/sql/ut_init.sql +++ b/sql/ut_init.sql @@ -50,8 +50,9 @@ CREATE TABLE s1.p2c3c2 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 250)) INHERITS(s CREATE TABLE s1.r1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1)); CREATE TABLE s1.r2 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1)); CREATE TABLE s1.r3 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1)); +CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL); -INSERT INTO s1.t1 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 1000) i) t; +INSERT INTO s1.t1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; INSERT INTO s1.t2 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.t3 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.t4 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; @@ -75,7 +76,7 @@ INSERT INTO s1.r1 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) INSERT INTO s1.r2 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; INSERT INTO s1.r3 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; -CREATE INDEX t1_i ON s1.t1 (c2); +CREATE INDEX t1_i ON s1.t1 (c3); CREATE INDEX t2_i ON s1.t2 (c2); CREATE INDEX t3_i ON s1.t3 (c2); CREATE INDEX t4_i ON s1.t4 (c2); @@ -110,6 +111,10 @@ CREATE VIEW s1.v3 AS SELECT v3t1.c1, v3t1.c2, v3t1.c3, v3t1.c4 FROM s1.t1 v3t1 J ANALYZE; +CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$ +VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3') +$$ LANGUAGE sql; + \q CREATE RULE r1_s_a AS ON SELECT TO s1.r1 WHERE true DO ALSO SELECT * FROM t1; CREATE RULE r1_s_i AS ON SELECT TO s1.r1 WHERE true DO INSTEAD SELECT * FROM t1; -- 2.11.0