1 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
3 ------------------------------------------------------------------------------
4 Merge Join (cost=0.00..90.08 rows=1000 width=16)
5 Merge Cond: (t1.id = t2.id)
6 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
7 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
10 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
12 -----------------------------------------------------------------------------------
13 Merge Join (cost=0.00..1377.64 rows=100000 width=16)
14 Merge Cond: (t2.val = t1.val)
15 -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8)
16 -> Materialize (cost=0.00..475.21 rows=10000 width=8)
17 -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8)
21 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
23 ------------------------------------------------------------------------------
24 Merge Join (cost=0.00..90.08 rows=1000 width=16)
25 Merge Cond: (t1.id = t2.id)
26 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
27 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
30 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
32 -----------------------------------------------------------------------------------
33 Merge Join (cost=0.00..1377.64 rows=100000 width=16)
34 Merge Cond: (t2.val = t1.val)
35 -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8)
36 -> Materialize (cost=0.00..475.21 rows=10000 width=8)
37 -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8)
41 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
42 INFO: hint syntax error at or near "Test (t1 t2) "
43 DETAIL: Keyword "Test" does not exist.
45 ------------------------------------------------------------------------------
46 Merge Join (cost=0.00..90.08 rows=1000 width=16)
47 Merge Cond: (t1.id = t2.id)
48 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
49 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
52 SET pg_hint_plan.enable TO off;
54 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
56 ------------------------------------------------------------------------------
57 Merge Join (cost=0.00..90.08 rows=1000 width=16)
58 Merge Cond: (t1.id = t2.id)
59 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
60 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
63 SET pg_hint_plan.enable TO on;
64 /*Set(enable_indexscan off)*/
65 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
67 ------------------------------------------------------------------
68 Hash Join (cost=27.50..220.00 rows=1000 width=16)
69 Hash Cond: (t1.id = t2.id)
70 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
71 -> Hash (cost=15.00..15.00 rows=1000 width=8)
72 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
75 /* Set(enable_indexscan off) Set(enable_hashjoin off) */
76 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
78 --------------------------------------------------------------------
79 Merge Join (cost=874.21..894.21 rows=1000 width=16)
80 Merge Cond: (t1.id = t2.id)
81 -> Sort (cost=809.39..834.39 rows=10000 width=8)
83 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
84 -> Sort (cost=64.83..67.33 rows=1000 width=8)
86 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
89 /* Set ( enable_indexscan off ) */
90 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
92 ------------------------------------------------------------------
93 Hash Join (cost=27.50..220.00 rows=1000 width=16)
94 Hash Cond: (t1.id = t2.id)
95 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
96 -> Hash (cost=15.00..15.00 rows=1000 width=8)
97 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
107 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
109 ------------------------------------------------------------------
110 Hash Join (cost=27.50..220.00 rows=1000 width=16)
111 Hash Cond: (t1.id = t2.id)
112 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
113 -> Hash (cost=15.00..15.00 rows=1000 width=8)
114 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
117 /* Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
118 Set(enable_seqscan off)
120 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
122 -----------------------------------------------------------------------------------------------------
123 Hash Join (cost=20000000055.75..20000000421.51 rows=1000 width=16)
124 Hash Cond: (t1.id = t2.id)
125 -> Index Scan using t1_pkey on t1 (cost=10000000000.00..10000000318.26 rows=10000 width=8)
126 -> Hash (cost=10000000043.25..10000000043.25 rows=1000 width=8)
127 -> Index Scan using t2_pkey on t2 (cost=10000000000.00..10000000043.25 rows=1000 width=8)
130 /*Set(work_mem "1M")*/
131 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
132 INFO: invalid value for parameter "work_mem": "1M"
133 HINT: Valid units for this parameter are "kB", "MB", and "GB".
135 ------------------------------------------------------------------------------
136 Merge Join (cost=0.00..90.08 rows=1000 width=16)
137 Merge Cond: (t1.id = t2.id)
138 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
139 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
142 /*Set(work_mem "1MB")*/
143 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
145 ------------------------------------------------------------------------------
146 Merge Join (cost=0.00..90.08 rows=1000 width=16)
147 Merge Cond: (t1.id = t2.id)
148 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
149 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
152 /*Set(work_mem TO "1MB")*/
153 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
154 INFO: hint syntax error at or near ""1MB")"
155 DETAIL: Closed parenthesis is necessary.
157 ------------------------------------------------------------------------------
158 Merge Join (cost=0.00..90.08 rows=1000 width=16)
159 Merge Cond: (t1.id = t2.id)
160 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
161 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)