SET search_path TO public;
+SET client_min_messages TO log;
+\set SHOW_CONTEXT always
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print TO on;
-SET client_min_messages TO LOG;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
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 * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
-- single table scan hint test
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_1)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_2)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(t1)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_1)BitmapScan(v_2)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_1)BitmapScan(t1)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_2)BitmapScan(t1)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
/*+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)*/
-EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
+EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
-- full scan hint pattern test
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
/*+NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
--- inherite table test
+-- inheritance tables test
SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
/*+Leading((((t5 t4)t3)(t2 t1)))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+
+-- inherite table test to specify the index's name
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_val_id_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_val)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_id2_val)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_val2_id)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_c1_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_pkey no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_c1_id_val_idx no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+
+/*+IndexScan(p2 p2_val_idx)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_expr)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_val_idx6)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+/*+IndexScan(p2 p2_val_idx p2_val_idx6)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+
+-- regular expression
+-- ordinary table
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexOnlyScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexOnlyScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexOnlyScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexOnlyScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ IndexOnlyScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ BitmapScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ BitmapScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ BitmapScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ BitmapScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+/*+ BitmapScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+
+-- Inheritance
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexScanRegexp(p1 p1[^_].*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexScan(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexOnlyScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexOnlyScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexOnlyScanRegexp(p1 p1[^_].*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ IndexOnlyScan(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ BitmapScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ BitmapScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+/*+ BitmapScanRegexp(p1 p1[^_].*)*/
+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 (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;', '', 'IndexScan(t1)');
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN SELECT * FROM t1 WHERE t1.id = ?;', '', 'BitmapScan(t1)');
+SELECT * FROM hint_plan.hints ORDER BY id;
+SET pg_hint_plan.enable_hint_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+SET pg_hint_plan.enable_hint_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE hint_plan.hints;
+
+-- plpgsql test
+EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
+
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
+DECLARE
+ ret record;
+BEGIN
+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+ RETURN ret;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+BEGIN
+ sum := 0;
+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+ i int;
+BEGIN
+ sum := 0;
+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+ rec record;
+ sum int := 0;
+BEGIN
+ FOR rec IN ref LOOP
+ sum := sum + rec.val;
+ END LOOP;
+ RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+
+DROP FUNCTION testfunc();
+DROP EXTENSION pg_hint_plan;
+
+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
+DECLARE
+ rows int;
+BEGIN
+ rows = 1;
+ while rows > 0 LOOP
+ PERFORM pg_stat_reset();
+ PERFORM pg_sleep(0.5);
+ SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
+DECLARE c int;
+BEGIN
+ EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
+ INTO c USING x;
+ RETURN c;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+vacuum analyze t1;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+
+-- Looped dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
+DECLARE
+ c text;
+ s int;
+BEGIN
+ r := 0;
+ FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
+ FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
+ r := r + s;
+ END LOOP;
+ END LOOP;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- one of the index scans happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- the index scan happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+
+-- Subqueries on inheritance tables under UNION
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+-- union all case
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+--
+-- Rows hint tests
+--
+-- Explain result includes "Planning time" if COSTS is enabled, but
+-- this test needs it enabled for get rows count. So do tests via psql
+-- and grep -v the mutable line.
+
+-- value types
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 +99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *0.01) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #aa) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 /99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+-- round up to 1
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99999) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+-- complex join tree
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #22) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t3 *10) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+\! rm results/pg_hint_plan.tmpout
+
+-- hint error level
+set client_min_messages to 'DEBUG1';
+set pg_hint_plan.debug_level to 'verbose';
+/*+ SeqScan( */ SELECT 1;
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+set pg_hint_plan.message_level to 'DEBUG1';
+set pg_hint_plan.parse_messages to 'NOTICE';
+/*+ SeqScan( */ SELECT 1;
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+
OSDN Git Service
