1 -- ut-T: tests for table hints
2 -- This test is focusing on hint retrieval from table
4 SET pg_hint_plan.enable_hint TO on;
5 SET pg_hint_plan.debug_print TO on;
6 SET client_min_messages TO LOG;
7 SET search_path TO public;
8 -- test for get_query_string
9 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
10 INSERT INTO hint_plan.hints VALUES(DEFAULT,'PREPARE p1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
11 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
12 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
13 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
14 -- These queries uses IndexScan without hints
15 SET pg_hint_plan.enable_hint_table to off;
16 EXPLAIN SELECT * FROM t1 WHERE id = 100;
18 ------------------------------------------------------------------
19 Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
20 Index Cond: (id = 100)
23 EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
25 ------------------------------------------------------------------
26 Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
27 Index Cond: (id = 100)
30 EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
32 ------------------------------------------------------------------
33 Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
34 Index Cond: (id = 100)
39 ------------------------------------------------------------------
40 Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
41 Index Cond: (id = 100)
45 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
46 EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
48 ------------------------------------------------------------------
49 Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=8)
50 Index Cond: (id = 100)
54 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
55 -- Forced to use SeqScan by table hints
56 SET pg_hint_plan.enable_hint_table to on;
57 EXPLAIN SELECT * FROM t1 WHERE id = 100;
66 ----------------------------------------------------
67 Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
71 EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
80 ----------------------------------------------------
81 Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
85 EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
94 ----------------------------------------------------
95 Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
108 ----------------------------------------------------
109 Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
114 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
115 EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
124 ----------------------------------------------------
125 Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
130 SET pg_hint_plan.enable_hint_table to off;
131 DELETE FROM hint_plan.hints;