-> Seq Scan on t2
(5 rows)
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+LOG: pg_hint_plan:
+used hint:
+Set(enable_indexscan off)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
/*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
Filter: (val = 1)
(20 rows)
+-- search from hint table
+INSERT INTO hint_plan.hints VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+SET pg_hint_plan.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (id = 1)
+(2 rows)
+
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using t1_pkey on t1
+ Index Cond: (id = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using t1_pkey on t1 (cost=0.00..8.27 rows=1 width=4)
+ Index Cond: (id = 1)
+(2 rows)
+
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+ id integer;
+BEGIN
+ SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+ RETURN;
+END;
+$$;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
+PL/pgSQL function "inline_code_block" line 5 at SQL statement
+RESET client_min_messages;
-> Seq Scan on t2
(5 rows)
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+LOG: pg_hint_plan:
+used hint:
+Set(enable_indexscan off)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
/*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
Filter: (val = 1)
(20 rows)
+-- search from hint table
+INSERT INTO hint_plan.hints VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+SET pg_hint_plan.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (id = 1)
+(2 rows)
+
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+ QUERY PLAN
+--------------------------------
+ Index Scan using t1_pkey on t1
+ Index Cond: (id = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Index Only Scan using t1_pkey on t1 (cost=0.00..4.28 rows=1 width=4)
+ Index Cond: (id = 1)
+(2 rows)
+
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+ id integer;
+BEGIN
+ SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+ RETURN;
+END;
+$$;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
+PL/pgSQL function inline_code_block line 5 at SQL statement
+RESET client_min_messages;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
/*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
/*+ BitmapScan(p1 p1_.*val2.*)*/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+
+-- search from hint table
+INSERT INTO hint_plan.hints VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+SET pg_hint_plan.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+ id integer;
+BEGIN
+ SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+ RETURN;
+END;
+$$;
+RESET client_min_messages;