1 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
2 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
5 SET pg_hint_plan.debug_print TO on;
6 SET client_min_messages TO LOG;
8 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
9 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
12 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
13 SET pg_hint_plan.enable TO off;
15 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
16 SET pg_hint_plan.enable TO on;
18 /*+Set(enable_indexscan off)*/
19 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
20 /*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
21 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
23 /*+ Set ( enable_indexscan off ) */
24 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
32 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
33 /*+ Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
34 Set(enable_seqscan off)
36 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
37 /*+Set(work_mem "1M")*/
38 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
39 /*+Set(work_mem "1MB")*/
40 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
41 /*+Set(work_mem TO "1MB")*/
42 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
45 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
47 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
48 /*+SeqScan(t1)IndexScan(t2)*/
49 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
51 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
52 /*+BitmapScan(t2)NoSeqScan(t1)*/
53 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
55 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
58 EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10;
60 EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
62 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
65 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
67 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
68 /*+NoMergeJoin(t1 t2)*/
69 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
72 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
74 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
75 /*+NoHashJoin(t1 t3)*/
76 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
78 /*+MergeJoin(t4 t1 t2 t3)*/
79 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
80 /*+HashJoin(t3 t4 t1 t2)*/
81 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
82 /*+NestLoop(t2 t3 t4 t1) IndexScan(t3)*/
83 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
84 /*+NoNestLoop(t4 t1 t3 t2)*/
85 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
88 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
89 /*+Leading(t3 t4 t1)*/
90 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
91 /*+Leading(t3 t4 t1 t2)*/
92 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
93 /*+Leading(t3 t4 t1 t2 t1)*/
94 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
95 /*+Leading(t3 t4 t4)*/
96 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
98 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
100 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
101 /*+HashJoin(t1 *VALUES*)*/
102 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
103 /*+HashJoin(t1 *VALUES*) IndexScan(t1) IndexScan(*VALUES*)*/
104 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
106 -- single table scan hint test
107 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);
109 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);
111 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);
113 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);
114 /*+BitmapScan(v_1)BitmapScan(v_2)*/
115 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);
116 /*+BitmapScan(v_1)BitmapScan(t1)*/
117 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);
118 /*+BitmapScan(v_2)BitmapScan(t1)*/
119 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);
120 /*+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)*/
121 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);