expected/ut-R.out

   1 LOAD 'pg_hint_plan';
   2 SET pg_hint_plan.enable_hint TO on;
   3 SET pg_hint_plan.debug_print TO on;
   4 SET client_min_messages TO LOG;
   5 SET search_path TO public;
   6 \o results/R_sample.out.log
   7 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
   8 \o
   9 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_sample.out.log > results/R_sample.out
  10 \! diff expected/R_sample.out results/R_sample.out
  11 ----
  12 ---- No. R-1-1 specified pattern of the object name
  13 ----
  14 -- No. R-1-1-1
  15 \o results/R_1-1-1.out.log
  16 /*+Rows(t1 t2 #1)*/
  17 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
  18 LOG:  pg_hint_plan:
  19 used hint:
  20 Rows(t1 t2 #1)
  21 not used hint:
  22 duplication hint:
  23 error hint:
  24
  25 \o
  26 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-1.out.log > results/R_1-1-1.out
  27 \! diff expected/R_1-1-1.out results/R_1-1-1.out
  28 -- No. R-1-1-2
  29 \o results/R_1-1-2.out.log
  30 /*+Rows(t1 t2 #1)*/
  31 EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
  32 LOG:  pg_hint_plan:
  33 used hint:
  34 not used hint:
  35 Rows(t1 t2 #1)
  36 duplication hint:
  37 error hint:
  38
  39 \o
  40 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-2.out.log > results/R_1-1-2.out
  41 \! diff expected/R_1-1-2.out results/R_1-1-2.out
  42 -- No. R-1-1-3
  43 \o results/R_1-1-3.out.log
  44 /*+Rows(t_1 t_2 #1)*/
  45 EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
  46 LOG:  pg_hint_plan:
  47 used hint:
  48 Rows(t_1 t_2 #1)
  49 not used hint:
  50 duplication hint:
  51 error hint:
  52
  53 \o
  54 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-3.out.log > results/R_1-1-3.out
  55 \! diff expected/R_1-1-3.out results/R_1-1-3.out
  56 ----
  57 ---- No. R-1-2 specified schema name in the hint option
  58 ----
  59 -- No. R-1-2-1
  60 \o results/R_1-2-1.out.log
  61 /*+Rows(t1 t2 #1)*/
  62 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
  63 LOG:  pg_hint_plan:
  64 used hint:
  65 Rows(t1 t2 #1)
  66 not used hint:
  67 duplication hint:
  68 error hint:
  69
  70 \o
  71 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-1.out.log > results/R_1-2-1.out
  72 \! diff expected/R_1-2-1.out results/R_1-2-1.out
  73 -- No. R-1-2-2
  74 \o results/R_1-2-2.out.log
  75 /*+Rows(s1.t1 s1.t2 #1)*/
  76 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
  77 LOG:  pg_hint_plan:
  78 used hint:
  79 not used hint:
  80 Rows(s1.t1 s1.t2 #1)
  81 duplication hint:
  82 error hint:
  83
  84 \o
  85 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-2.out.log > results/R_1-2-2.out
  86 \! diff expected/R_1-2-2.out results/R_1-2-2.out
  87 ----
  88 ---- No. R-1-3 table doesn't exist in the hint option
  89 ----
  90 -- No. R-1-3-1
  91 \o results/R_1-3-1.out.log
  92 /*+Rows(t1 t2 #1)*/
  93 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
  94 LOG:  pg_hint_plan:
  95 used hint:
  96 Rows(t1 t2 #1)
  97 not used hint:
  98 duplication hint:
  99 error hint:
 100
 101 \o
 102 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-1.out.log > results/R_1-3-1.out
 103 \! diff expected/R_1-3-1.out results/R_1-3-1.out
 104 -- No. R-1-3-2
 105 \o results/R_1-3-2.out.log
 106 /*+Rows(t3 t4 #1)*/
 107 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 108 LOG:  pg_hint_plan:
 109 used hint:
 110 not used hint:
 111 Rows(t3 t4 #1)
 112 duplication hint:
 113 error hint:
 114
 115 \o
 116 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-2.out.log > results/R_1-3-2.out
 117 \! diff expected/R_1-3-2.out results/R_1-3-2.out
 118 ----
 119 ---- No. R-1-4 conflict table name
 120 ----
 121 -- No. R-1-4-1
 122 \o results/R_1-4-1.out.log
 123 /*+Rows(t1 t2 #1)*/
 124 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 125 LOG:  pg_hint_plan:
 126 used hint:
 127 Rows(t1 t2 #1)
 128 not used hint:
 129 duplication hint:
 130 error hint:
 131
 132 \o
 133 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-1.out.log > results/R_1-4-1.out
 134 \! diff expected/R_1-4-1.out results/R_1-4-1.out
 135 -- No. R-1-4-2
 136 \o results/R_1-4-2.out.log
 137 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
 138 /*+Rows(t1 t1 #1)*/
 139 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
 140 INFO:  hint syntax error at or near "Rows(t1 t1 #1)"
 141 DETAIL:  Relation name "t1" is ambiguous.
 142 LOG:  pg_hint_plan:
 143 used hint:
 144 not used hint:
 145 duplication hint:
 146 error hint:
 147 Rows(t1 t1 #1)
 148
 149 /*+Rows(s1.t1 s2.t1 #1)*/
 150 EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
 151 LOG:  pg_hint_plan:
 152 used hint:
 153 not used hint:
 154 Rows(s1.t1 s2.t1 #1)
 155 duplication hint:
 156 error hint:
 157
 158 EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
 159 /*+Rows(t1 s2t1 #1)*/
 160 EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
 161 LOG:  pg_hint_plan:
 162 used hint:
 163 Rows(s2t1 t1 #1)
 164 not used hint:
 165 duplication hint:
 166 error hint:
 167
 168 \o
 169 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-2.out.log > results/R_1-4-2.out
 170 \! diff expected/R_1-4-2.out results/R_1-4-2.out
 171 -- No. R-1-4-3
 172 \o results/R_1-4-3.out.log
 173 EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 174 /*+Rows(t1 t2 #1)*/
 175 EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 176 LOG:  pg_hint_plan:
 177 used hint:
 178 Rows(t1 t2 #1)
 179 not used hint:
 180 duplication hint:
 181 error hint:
 182
 183 /*+Rows(st1 st2 #1)Rows(t1 t2 #1)*/
 184 EXPLAIN SELECT *, (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 185 LOG:  pg_hint_plan:
 186 used hint:
 187 Rows(st1 st2 #1)
 188 Rows(t1 t2 #1)
 189 not used hint:
 190 duplication hint:
 191 error hint:
 192
 193 \o
 194 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-3.out.log > results/R_1-4-3.out
 195 \! diff expected/R_1-4-3.out results/R_1-4-3.out
 196 ----
 197 ---- No. R-1-5 conflict table name
 198 ----
 199 -- No. R-1-5-1
 200 \o results/R_1-5-1.out.log
 201 /*+Rows(t1 t2 #1)*/
 202 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 203 LOG:  pg_hint_plan:
 204 used hint:
 205 Rows(t1 t2 #1)
 206 not used hint:
 207 duplication hint:
 208 error hint:
 209
 210 \o
 211 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-1.out.log > results/R_1-5-1.out
 212 \! diff expected/R_1-5-1.out results/R_1-5-1.out
 213 -- No. R-1-5-2
 214 \o results/R_1-5-2.out.log
 215 /*+Rows(t1 t1 #1)*/
 216 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 217 INFO:  hint syntax error at or near "Rows(t1 t1 #1)"
 218 DETAIL:  Relation name "t1" is duplicated.
 219 LOG:  pg_hint_plan:
 220 used hint:
 221 not used hint:
 222 duplication hint:
 223 error hint:
 224 Rows(t1 t1 #1)
 225
 226 \o
 227 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-2.out.log > results/R_1-5-2.out
 228 \! diff expected/R_1-5-2.out results/R_1-5-2.out
 229 -- No. R-1-5-3
 230 \o results/R_1-5-3.out.log
 231 /*+(t1 t1)(t2 t2)*/
 232 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 233 INFO:  hint syntax error at or near "(t1 t1)(t2 t2)"
 234 DETAIL:  Unrecognized hint keyword "".
 235 EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
 236 /*+(t1 t2 t1 t2)*/
 237 EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
 238 INFO:  hint syntax error at or near "(t1 t2 t1 t2)"
 239 DETAIL:  Unrecognized hint keyword "".
 240 \o
 241 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-3.out.log > results/R_1-5-3.out
 242 \! diff expected/R_1-5-3.out results/R_1-5-3.out
 243 ----
 244 ---- No. R-1-6 object type for the hint
 245 ----
 246 -- No. R-1-6-1
 247 \o results/R_1-6-1.out.log
 248 /*+Rows(t1 t2 #1)*/
 249 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 250 LOG:  pg_hint_plan:
 251 used hint:
 252 Rows(t1 t2 #1)
 253 not used hint:
 254 duplication hint:
 255 error hint:
 256
 257 \o
 258 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-1.out.log > results/R_1-6-1.out
 259 \! diff expected/R_1-6-1.out results/R_1-6-1.out
 260 -- No. R-1-6-2
 261 \o results/R_1-6-2.out.log
 262 EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
 263 /*+Rows(t1 t2 #1)*/
 264 EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
 265 LOG:  pg_hint_plan:
 266 used hint:
 267 Rows(t1 t2 #1)
 268 not used hint:
 269 duplication hint:
 270 error hint:
 271
 272 \o
 273 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-2.out.log > results/R_1-6-2.out
 274 \! diff expected/R_1-6-2.out results/R_1-6-2.out
 275 -- No. R-1-6-3
 276 \o results/R_1-6-3.out.log
 277 EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
 278 /*+Rows(t1 t2 #1)*/
 279 EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
 280 LOG:  pg_hint_plan:
 281 used hint:
 282 Rows(t1 t2 #1)
 283 not used hint:
 284 duplication hint:
 285 error hint:
 286
 287 \o
 288 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-3.out.log > results/R_1-6-3.out
 289 \! diff expected/R_1-6-3.out results/R_1-6-3.out
 290 -- No. R-1-6-4
 291 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
 292 \o results/R_1-6-4.out.log
 293 EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
 294 /*+Rows(t1 t2 #1)*/
 295 EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
 296 LOG:  pg_hint_plan:
 297 used hint:
 298 Rows(t1 t2 #1)
 299 not used hint:
 300 duplication hint:
 301 error hint:
 302
 303 \o
 304 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-4.out.log > results/R_1-6-4.out
 305 \! diff expected/R_1-6-4.out results/R_1-6-4.out
 306 -- No. R-1-6-5
 307 \o results/R_1-6-5.out.log
 308 EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
 309 /*+Rows(t1 t2 #1)*/
 310 EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
 311 LOG:  pg_hint_plan:
 312 used hint:
 313 Rows(t1 t2 #1)
 314 not used hint:
 315 duplication hint:
 316 error hint:
 317
 318 \o
 319 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-5.out.log > results/R_1-6-5.out
 320 \! diff expected/R_1-6-5.out results/R_1-6-5.out
 321 -- No. R-1-6-6
 322 -- refer ut-fdw.sql
 323 -- No. R-1-6-7
 324 \o results/R_1-6-7.out.log
 325 EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
 326 /*+Rows(t1 t2 #1)*/
 327 EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
 328 LOG:  pg_hint_plan:
 329 used hint:
 330 Rows(t1 t2 #1)
 331 not used hint:
 332 duplication hint:
 333 error hint:
 334
 335 \o
 336 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-7.out.log > results/R_1-6-7.out
 337 \! diff expected/R_1-6-7.out results/R_1-6-7.out
 338 -- No. R-1-6-8
 339 \o results/R_1-6-8.out.log
 340 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4),  s1.t2 WHERE t1.c1 = t2.c1;
 341 /*+Rows(t1 t2 #1)*/
 342 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4),  s1.t2 WHERE t1.c1 = t2.c1;
 343 LOG:  pg_hint_plan:
 344 used hint:
 345 not used hint:
 346 Rows(t1 t2 #1)
 347 duplication hint:
 348 error hint:
 349
 350 /*+Rows(*VALUES* t2 #1)*/
 351 EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4),  s1.t2 WHERE t1.c1 = t2.c1;
 352 LOG:  pg_hint_plan:
 353 used hint:
 354 Rows(*VALUES* t2 #1)
 355 not used hint:
 356 duplication hint:
 357 error hint:
 358
 359 \o
 360 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-8.out.log > results/R_1-6-8.out
 361 \! diff expected/R_1-6-8.out results/R_1-6-8.out
 362 -- No. R-1-6-9
 363 \o results/R_1-6-9.out.log
 364 EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
 365 /*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/
 366 EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
 367 LOG:  pg_hint_plan:
 368 used hint:
 369 Rows(c1 t1 +1)
 370 Rows(t1 t2 #1)
 371 not used hint:
 372 duplication hint:
 373 error hint:
 374
 375 \o
 376 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-9.out.log > results/R_1-6-9.out
 377 \! diff expected/R_1-6-9.out results/R_1-6-9.out
 378 -- No. R-1-6-10
 379 \o results/R_1-6-10.out.log
 380 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
 381 /*+Rows(t1 t2 #1)*/
 382 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
 383 LOG:  pg_hint_plan:
 384 used hint:
 385 not used hint:
 386 Rows(t1 t2 #1)
 387 duplication hint:
 388 error hint:
 389
 390 /*+Rows(v1t1 v1t1_ #1)*/
 391 EXPLAIN SELECT * FROM s1.v1 t1, s1.v1_ t2 WHERE t1.c1 = t2.c1;
 392 LOG:  pg_hint_plan:
 393 used hint:
 394 Rows(v1t1 v1t1_ #1)
 395 not used hint:
 396 duplication hint:
 397 error hint:
 398
 399 \o
 400 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-10.out.log > results/R_1-6-10.out
 401 \! diff expected/R_1-6-10.out results/R_1-6-10.out
 402 -- No. R-1-6-11
 403 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
 404                                            QUERY PLAN
 405 -------------------------------------------------------------------------------------------------
 406  Nested Loop  (cost=12.28..22.56 rows=1 width=29)
 407    InitPlan 1 (returns $0)
 408      ->  Aggregate  (cost=12.00..12.01 rows=1 width=4)
 409            ->  Merge Join  (cost=5.60..11.75 rows=100 width=4)
 410                  Merge Cond: (st1.c1 = st2.c1)
 411                  ->  Index Only Scan using t1_i1 on t1 st1  (cost=0.28..44.27 rows=1000 width=4)
 412                  ->  Sort  (cost=5.32..5.57 rows=100 width=4)
 413                        Sort Key: st2.c1
 414                        ->  Seq Scan on t2 st2  (cost=0.00..2.00 rows=100 width=4)
 415    ->  Index Scan using t1_i1 on t1  (cost=0.28..8.29 rows=1 width=15)
 416          Index Cond: (c1 = $0)
 417    ->  Seq Scan on t2  (cost=0.00..2.25 rows=1 width=14)
 418          Filter: (c1 = $0)
 419 (13 rows)
 420
 421 /*+Rows(t1 t2 #1)Rows(st1 st2 #1)*/
 422 EXPLAIN (COSTS true) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
 423 LOG:  pg_hint_plan:
 424 used hint:
 425 Rows(st1 st2 #1)
 426 Rows(t1 t2 #1)
 427 not used hint:
 428 duplication hint:
 429 error hint:
 430
 431                                            QUERY PLAN
 432 -------------------------------------------------------------------------------------------------
 433  Nested Loop  (cost=12.03..22.31 rows=1 width=29)
 434    InitPlan 1 (returns $0)
 435      ->  Aggregate  (cost=11.75..11.76 rows=1 width=4)
 436            ->  Merge Join  (cost=5.60..11.75 rows=1 width=4)
 437                  Merge Cond: (st1.c1 = st2.c1)
 438                  ->  Index Only Scan using t1_i1 on t1 st1  (cost=0.28..44.27 rows=1000 width=4)
 439                  ->  Sort  (cost=5.32..5.57 rows=100 width=4)
 440                        Sort Key: st2.c1
 441                        ->  Seq Scan on t2 st2  (cost=0.00..2.00 rows=100 width=4)
 442    ->  Index Scan using t1_i1 on t1  (cost=0.28..8.29 rows=1 width=15)
 443          Index Cond: (c1 = $0)
 444    ->  Seq Scan on t2  (cost=0.00..2.25 rows=1 width=14)
 445          Filter: (c1 = $0)
 446 (13 rows)
 447
 448 --
 449 -- There are cases where difference in the measured value and predicted value
 450 -- depending upon the version of PostgreSQL
 451 --
 452 \o results/R_1-6-11.out.log
 453 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
 454 /*+Rows(t1 st2 #1)*/
 455 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
 456 LOG:  pg_hint_plan:
 457 used hint:
 458 not used hint:
 459 Rows(st2 t1 #1)
 460 duplication hint:
 461 error hint:
 462
 463 /*+Rows(t1 t2 #1)*/
 464 EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
 465 LOG:  pg_hint_plan:
 466 used hint:
 467 Rows(t1 t2 #1)
 468 not used hint:
 469 duplication hint:
 470 error hint:
 471
 472 \o
 473 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-11.out.log > results/R_1-6-11.out
 474 \! diff expected/R_1-6-11.out results/R_1-6-11.out
 475 ----
 476 ---- No. R-1-7 specified number of conditions
 477 ----
 478 -- No. R-1-7-1
 479 \o results/R_1-7-1.out.log
 480 /*+Rows(t1 #1)*/
 481 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 482 INFO:  hint syntax error at or near ""
 483 DETAIL:  Rows hint requires at least two relations.
 484 LOG:  pg_hint_plan:
 485 used hint:
 486 not used hint:
 487 duplication hint:
 488 error hint:
 489 Rows(t1 #1)
 490
 491 \o
 492 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-1.out.log > results/R_1-7-1.out
 493 \! diff expected/R_1-7-1.out results/R_1-7-1.out
 494 -- No. R-1-7-2
 495 \o results/R_1-7-2.out.log
 496 /*+Rows(t1 t2 1)*/
 497 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 498 INFO:  hint syntax error at or near "1"
 499 DETAIL:  unrecognized rows value type notation.
 500 LOG:  pg_hint_plan:
 501 used hint:
 502 not used hint:
 503 duplication hint:
 504 error hint:
 505 Rows(t1 t2 1)
 506
 507 \o
 508 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-2.out.log > results/R_1-7-2.out
 509 \! diff expected/R_1-7-2.out results/R_1-7-2.out
 510 -- No. R-1-7-3
 511 \o results/R_1-7-3.out.log
 512 /*+Rows(t1 t2 #notrows)*/
 513 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
 514 INFO:  hint syntax error at or near "notrows"
 515 DETAIL:  Rows hint requires valid number as rows estimation.
 516 LOG:  pg_hint_plan:
 517 used hint:
 518 not used hint:
 519 duplication hint:
 520 error hint:
 521 Rows(t1 t2 #notrows)
 522
 523 \o
 524 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-3.out.log > results/R_1-7-3.out
 525 \! diff expected/R_1-7-3.out results/R_1-7-3.out
 526 ----
 527 ---- No. R-2-1 some complexity query blocks
 528 ----
 529 -- No. R-2-1-1
 530 \o results/R_2-1-1.out.log
 531 /*+
 532 Leading(bmt1 bmt2 bmt3 bmt4)
 533 Leading(b1t2 b1t3 b1t4 b1t1)
 534 Leading(b2t3 b2t4 b2t1 b2t2)
 535 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 536 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 537 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 538 */
 539 EXPLAIN
 540 SELECT max(bmt1.c1), (
 541 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 542 ), (
 543 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 544 )
 545                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 546 ;
 547 LOG:  pg_hint_plan:
 548 used hint:
 549 MergeJoin(b1t2 b1t3)
 550 MergeJoin(b2t3 b2t4)
 551 MergeJoin(bmt1 bmt2)
 552 HashJoin(b1t2 b1t3 b1t4)
 553 HashJoin(b2t1 b2t3 b2t4)
 554 HashJoin(bmt1 bmt2 bmt3)
 555 NestLoop(b1t1 b1t2 b1t3 b1t4)
 556 NestLoop(b2t1 b2t2 b2t3 b2t4)
 557 NestLoop(bmt1 bmt2 bmt3 bmt4)
 558 Leading(bmt1 bmt2 bmt3 bmt4)
 559 Leading(b1t2 b1t3 b1t4 b1t1)
 560 Leading(b2t3 b2t4 b2t1 b2t2)
 561 not used hint:
 562 duplication hint:
 563 error hint:
 564
 565 /*+
 566 Leading(bmt1 bmt2 bmt3 bmt4)
 567 Leading(b1t2 b1t3 b1t4 b1t1)
 568 Leading(b2t3 b2t4 b2t1 b2t2)
 569 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 570 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 571 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 572 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 573 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
 574 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
 575 */
 576 EXPLAIN
 577 SELECT max(bmt1.c1), (
 578 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 579 ), (
 580 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1)
 581                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 582 ;
 583 LOG:  pg_hint_plan:
 584 used hint:
 585 MergeJoin(b1t2 b1t3)
 586 MergeJoin(b2t3 b2t4)
 587 MergeJoin(bmt1 bmt2)
 588 HashJoin(b1t2 b1t3 b1t4)
 589 HashJoin(b2t1 b2t3 b2t4)
 590 HashJoin(bmt1 bmt2 bmt3)
 591 NestLoop(b1t1 b1t2 b1t3 b1t4)
 592 NestLoop(b2t1 b2t2 b2t3 b2t4)
 593 NestLoop(bmt1 bmt2 bmt3 bmt4)
 594 Leading(bmt1 bmt2 bmt3 bmt4)
 595 Leading(b1t2 b1t3 b1t4 b1t1)
 596 Leading(b2t3 b2t4 b2t1 b2t2)
 597 Rows(b1t2 b1t3 #1)
 598 Rows(b2t3 b2t4 #1)
 599 Rows(bmt1 bmt2 #1)
 600 Rows(b1t2 b1t3 b1t4 #1)
 601 Rows(b2t1 b2t3 b2t4 #1)
 602 Rows(bmt1 bmt2 bmt3 #1)
 603 Rows(b1t1 b1t2 b1t3 b1t4 #1)
 604 Rows(b2t1 b2t2 b2t3 b2t4 #1)
 605 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 606 not used hint:
 607 duplication hint:
 608 error hint:
 609
 610 \o
 611 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-1.out.log > results/R_2-1-1.out
 612 \! diff expected/R_2-1-1.out results/R_2-1-1.out
 613 -- No. R-2-1-2
 614 \o results/R_2-1-2.out.log
 615 /*+
 616 Leading(bmt1 bmt2 bmt3 bmt4)
 617 Leading(b1t2 b1t3 b1t4 b1t1)
 618 Leading(b2t3 b2t4 b2t1 b2t2)
 619 Leading(b3t4 b3t1 b3t2 b3t3)
 620 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 621 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 622 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 623 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
 624 */
 625 EXPLAIN
 626 SELECT max(bmt1.c1), (
 627 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 628 ), (
 629 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 630 ), (
 631 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
 632 )
 633                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 634 ;
 635 LOG:  pg_hint_plan:
 636 used hint:
 637 MergeJoin(b1t2 b1t3)
 638 MergeJoin(b2t3 b2t4)
 639 MergeJoin(b3t1 b3t4)
 640 MergeJoin(bmt1 bmt2)
 641 HashJoin(b1t2 b1t3 b1t4)
 642 HashJoin(b2t1 b2t3 b2t4)
 643 HashJoin(b3t1 b3t2 b3t4)
 644 HashJoin(bmt1 bmt2 bmt3)
 645 NestLoop(b1t1 b1t2 b1t3 b1t4)
 646 NestLoop(b2t1 b2t2 b2t3 b2t4)
 647 NestLoop(b3t1 b3t2 b3t3 b3t4)
 648 NestLoop(bmt1 bmt2 bmt3 bmt4)
 649 Leading(bmt1 bmt2 bmt3 bmt4)
 650 Leading(b1t2 b1t3 b1t4 b1t1)
 651 Leading(b2t3 b2t4 b2t1 b2t2)
 652 Leading(b3t4 b3t1 b3t2 b3t3)
 653 not used hint:
 654 duplication hint:
 655 error hint:
 656
 657 /*+
 658 Leading(bmt1 bmt2 bmt3 bmt4)
 659 Leading(b1t2 b1t3 b1t4 b1t1)
 660 Leading(b2t3 b2t4 b2t1 b2t2)
 661 Leading(b3t4 b3t1 b3t2 b3t3)
 662 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 663 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 664 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 665 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
 666 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 667 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
 668 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
 669 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
 670 */
 671 EXPLAIN
 672 SELECT max(bmt1.c1), (
 673 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 674 ), (
 675 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 676 ), (
 677 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
 678 )
 679                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 680 ;
 681 LOG:  pg_hint_plan:
 682 used hint:
 683 MergeJoin(b1t2 b1t3)
 684 MergeJoin(b2t3 b2t4)
 685 MergeJoin(b3t1 b3t4)
 686 MergeJoin(bmt1 bmt2)
 687 HashJoin(b1t2 b1t3 b1t4)
 688 HashJoin(b2t1 b2t3 b2t4)
 689 HashJoin(b3t1 b3t2 b3t4)
 690 HashJoin(bmt1 bmt2 bmt3)
 691 NestLoop(b1t1 b1t2 b1t3 b1t4)
 692 NestLoop(b2t1 b2t2 b2t3 b2t4)
 693 NestLoop(b3t1 b3t2 b3t3 b3t4)
 694 NestLoop(bmt1 bmt2 bmt3 bmt4)
 695 Leading(bmt1 bmt2 bmt3 bmt4)
 696 Leading(b1t2 b1t3 b1t4 b1t1)
 697 Leading(b2t3 b2t4 b2t1 b2t2)
 698 Leading(b3t4 b3t1 b3t2 b3t3)
 699 Rows(b1t2 b1t3 #1)
 700 Rows(b2t3 b2t4 #1)
 701 Rows(b3t1 b3t4 #1)
 702 Rows(bmt1 bmt2 #1)
 703 Rows(b1t2 b1t3 b1t4 #1)
 704 Rows(b2t1 b2t3 b2t4 #1)
 705 Rows(b3t1 b3t2 b3t4 #1)
 706 Rows(bmt1 bmt2 bmt3 #1)
 707 Rows(b1t1 b1t2 b1t3 b1t4 #1)
 708 Rows(b2t1 b2t2 b2t3 b2t4 #1)
 709 Rows(b3t1 b3t2 b3t3 b3t4 #1)
 710 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 711 not used hint:
 712 duplication hint:
 713 error hint:
 714
 715 \o
 716 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-2.out.log > results/R_2-1-2.out
 717 \! diff expected/R_2-1-2.out results/R_2-1-2.out
 718 -- No. R-2-1-3
 719 \o results/R_2-1-3.out.log
 720 /*+
 721 Leading(bmt4 bmt3 bmt2 bmt1)
 722 */
 723 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
 724 LOG:  pg_hint_plan:
 725 used hint:
 726 Leading(bmt4 bmt3 bmt2 bmt1)
 727 not used hint:
 728 duplication hint:
 729 error hint:
 730
 731 /*+
 732 Leading(bmt4 bmt3 bmt2 bmt1)
 733 Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 734 */
 735 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
 736 LOG:  pg_hint_plan:
 737 used hint:
 738 Leading(bmt4 bmt3 bmt2 bmt1)
 739 Rows(bmt3 bmt4 #1)
 740 Rows(bmt2 bmt3 bmt4 #1)
 741 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 742 not used hint:
 743 duplication hint:
 744 error hint:
 745
 746 \o
 747 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-3.out.log > results/R_2-1-3.out
 748 \! diff expected/R_2-1-3.out results/R_2-1-3.out
 749 -- No. R-2-1-4
 750 \o results/R_2-1-4.out.log
 751 /*+
 752 Leading(bmt4 bmt3 bmt2 bmt1)
 753 */
 754 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
 755 LOG:  pg_hint_plan:
 756 used hint:
 757 Leading(bmt4 bmt3 bmt2 bmt1)
 758 not used hint:
 759 duplication hint:
 760 error hint:
 761
 762 /*+
 763 Leading(bmt4 bmt3 bmt2 bmt1)
 764 Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 765 */
 766 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
 767 LOG:  pg_hint_plan:
 768 used hint:
 769 Leading(bmt4 bmt3 bmt2 bmt1)
 770 Rows(bmt3 bmt4 #1)
 771 Rows(bmt2 bmt3 bmt4 #1)
 772 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 773 not used hint:
 774 duplication hint:
 775 error hint:
 776
 777 \o
 778 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-4.out.log > results/R_2-1-4.out
 779 \! diff expected/R_2-1-4.out results/R_2-1-4.out
 780 -- No. R-2-1-5
 781 \o results/R_2-1-5.out.log
 782 /*+
 783 Leading(bmt1 bmt2 bmt3 bmt4)
 784 Leading(b1t2 b1t3 b1t4 b1t1)
 785 Leading(b2t3 b2t4 b2t1 b2t2)
 786 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 787 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 788 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 789 */
 790 EXPLAIN
 791 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 792 AND bmt1.c1 <> (
 793 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 794 ) AND bmt1.c1 <> (
 795 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 796 )
 797 ;
 798 LOG:  pg_hint_plan:
 799 used hint:
 800 MergeJoin(b1t2 b1t3)
 801 MergeJoin(b2t3 b2t4)
 802 MergeJoin(bmt1 bmt2)
 803 HashJoin(b1t2 b1t3 b1t4)
 804 HashJoin(b2t1 b2t3 b2t4)
 805 HashJoin(bmt1 bmt2 bmt3)
 806 NestLoop(b1t1 b1t2 b1t3 b1t4)
 807 NestLoop(b2t1 b2t2 b2t3 b2t4)
 808 NestLoop(bmt1 bmt2 bmt3 bmt4)
 809 Leading(bmt1 bmt2 bmt3 bmt4)
 810 Leading(b1t2 b1t3 b1t4 b1t1)
 811 Leading(b2t3 b2t4 b2t1 b2t2)
 812 not used hint:
 813 duplication hint:
 814 error hint:
 815
 816 /*+
 817 Leading(bmt1 bmt2 bmt3 bmt4)
 818 Leading(b1t2 b1t3 b1t4 b1t1)
 819 Leading(b2t3 b2t4 b2t1 b2t2)
 820 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 821 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 822 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 823 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 824 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
 825 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
 826 */
 827 EXPLAIN
 828 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 829 AND bmt1.c1 <> (
 830 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 831 ) AND bmt1.c1 <> (
 832 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 833 )
 834 ;
 835 LOG:  pg_hint_plan:
 836 used hint:
 837 MergeJoin(b1t2 b1t3)
 838 MergeJoin(b2t3 b2t4)
 839 MergeJoin(bmt1 bmt2)
 840 HashJoin(b1t2 b1t3 b1t4)
 841 HashJoin(b2t1 b2t3 b2t4)
 842 HashJoin(bmt1 bmt2 bmt3)
 843 NestLoop(b1t1 b1t2 b1t3 b1t4)
 844 NestLoop(b2t1 b2t2 b2t3 b2t4)
 845 NestLoop(bmt1 bmt2 bmt3 bmt4)
 846 Leading(bmt1 bmt2 bmt3 bmt4)
 847 Leading(b1t2 b1t3 b1t4 b1t1)
 848 Leading(b2t3 b2t4 b2t1 b2t2)
 849 Rows(b1t2 b1t3 #1)
 850 Rows(b2t3 b2t4 #1)
 851 Rows(bmt1 bmt2 #1)
 852 Rows(b1t2 b1t3 b1t4 #1)
 853 Rows(b2t1 b2t3 b2t4 #1)
 854 Rows(bmt1 bmt2 bmt3 #1)
 855 Rows(b1t1 b1t2 b1t3 b1t4 #1)
 856 Rows(b2t1 b2t2 b2t3 b2t4 #1)
 857 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 858 not used hint:
 859 duplication hint:
 860 error hint:
 861
 862 \o
 863 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-5.out.log > results/R_2-1-5.out
 864 \! diff expected/R_2-1-5.out results/R_2-1-5.out
 865 -- No. R-2-1-6
 866 \o results/R_2-1-6.out.log
 867 /*+
 868 Leading(bmt1 bmt2 bmt3 bmt4)
 869 Leading(b1t2 b1t3 b1t4 b1t1)
 870 Leading(b2t3 b2t4 b2t1 b2t2)
 871 Leading(b3t4 b3t1 b3t2 b3t3)
 872 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 873 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 874 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 875 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
 876 */
 877 EXPLAIN
 878 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 879   AND bmt1.c1 <> (
 880 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 881 ) AND bmt1.c1 <> (
 882 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 883 ) AND bmt1.c1 <> (
 884 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
 885 )
 886 ;
 887 LOG:  pg_hint_plan:
 888 used hint:
 889 MergeJoin(b1t2 b1t3)
 890 MergeJoin(b2t3 b2t4)
 891 MergeJoin(b3t1 b3t4)
 892 MergeJoin(bmt1 bmt2)
 893 HashJoin(b1t2 b1t3 b1t4)
 894 HashJoin(b2t1 b2t3 b2t4)
 895 HashJoin(b3t1 b3t2 b3t4)
 896 HashJoin(bmt1 bmt2 bmt3)
 897 NestLoop(b1t1 b1t2 b1t3 b1t4)
 898 NestLoop(b2t1 b2t2 b2t3 b2t4)
 899 NestLoop(b3t1 b3t2 b3t3 b3t4)
 900 NestLoop(bmt1 bmt2 bmt3 bmt4)
 901 Leading(bmt1 bmt2 bmt3 bmt4)
 902 Leading(b1t2 b1t3 b1t4 b1t1)
 903 Leading(b2t3 b2t4 b2t1 b2t2)
 904 Leading(b3t4 b3t1 b3t2 b3t3)
 905 not used hint:
 906 duplication hint:
 907 error hint:
 908
 909 /*+
 910 Leading(bmt1 bmt2 bmt3 bmt4)
 911 Leading(b1t2 b1t3 b1t4 b1t1)
 912 Leading(b2t3 b2t4 b2t1 b2t2)
 913 Leading(b3t4 b3t1 b3t2 b3t3)
 914 MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
 915 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 916 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 917 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
 918 Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
 919 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
 920 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
 921 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
 922 */
 923 EXPLAIN
 924 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 925   AND bmt1.c1 <> (
 926 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 927 ) AND bmt1.c1 <> (
 928 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 929 ) AND bmt1.c1 <> (
 930 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
 931 )
 932 ;
 933 LOG:  pg_hint_plan:
 934 used hint:
 935 MergeJoin(b1t2 b1t3)
 936 MergeJoin(b2t3 b2t4)
 937 MergeJoin(b3t1 b3t4)
 938 MergeJoin(bmt1 bmt2)
 939 HashJoin(b1t2 b1t3 b1t4)
 940 HashJoin(b2t1 b2t3 b2t4)
 941 HashJoin(b3t1 b3t2 b3t4)
 942 HashJoin(bmt1 bmt2 bmt3)
 943 NestLoop(b1t1 b1t2 b1t3 b1t4)
 944 NestLoop(b2t1 b2t2 b2t3 b2t4)
 945 NestLoop(b3t1 b3t2 b3t3 b3t4)
 946 NestLoop(bmt1 bmt2 bmt3 bmt4)
 947 Leading(bmt1 bmt2 bmt3 bmt4)
 948 Leading(b1t2 b1t3 b1t4 b1t1)
 949 Leading(b2t3 b2t4 b2t1 b2t2)
 950 Leading(b3t4 b3t1 b3t2 b3t3)
 951 Rows(b1t2 b1t3 #1)
 952 Rows(b2t3 b2t4 #1)
 953 Rows(b3t1 b3t4 #1)
 954 Rows(bmt1 bmt2 #1)
 955 Rows(b1t2 b1t3 b1t4 #1)
 956 Rows(b2t1 b2t3 b2t4 #1)
 957 Rows(b3t1 b3t2 b3t4 #1)
 958 Rows(bmt1 bmt2 bmt3 #1)
 959 Rows(b1t1 b1t2 b1t3 b1t4 #1)
 960 Rows(b2t1 b2t2 b2t3 b2t4 #1)
 961 Rows(b3t1 b3t2 b3t3 b3t4 #1)
 962 Rows(bmt1 bmt2 bmt3 bmt4 #1)
 963 not used hint:
 964 duplication hint:
 965 error hint:
 966
 967 \o
 968 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-6.out.log > results/R_2-1-6.out
 969 \! diff expected/R_2-1-6.out results/R_2-1-6.out
 970 -- No. R-2-1-7
 971 \o results/R_2-1-7.out.log
 972 /*+
 973 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
 974 Leading(b1t2 b1t3 b1t4 b1t1)
 975 Leading(b2t3 b2t4 b2t1 b2t2)
 976 MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4)
 977 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
 978 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
 979 */
 980 EXPLAIN
 981 WITH c1 (c1) AS (
 982 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
 983 )
 984 , c2 (c1) AS (
 985 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
 986 )
 987 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
 988 , c1, c2
 989                                                                         WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
 990 AND bmt1.c1 = c1.c1
 991 AND bmt1.c1 = c2.c1
 992 ;
 993 LOG:  pg_hint_plan:
 994 used hint:
 995 MergeJoin(b1t2 b1t3)
 996 MergeJoin(b2t3 b2t4)
 997 MergeJoin(c1 c2)
 998 HashJoin(b1t2 b1t3 b1t4)
 999 HashJoin(b2t1 b2t3 b2t4)
1000 HashJoin(bmt1 c1 c2)
1001 NestLoop(b1t1 b1t2 b1t3 b1t4)
1002 NestLoop(b2t1 b2t2 b2t3 b2t4)
1003 NestLoop(bmt1 bmt2 c1 c2)
1004 MergeJoin(bmt1 bmt2 bmt3 c1 c2)
1005 HashJoin(bmt1 bmt2 bmt3 bmt4 c1 c2)
1006 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
1007 Leading(b1t2 b1t3 b1t4 b1t1)
1008 Leading(b2t3 b2t4 b2t1 b2t2)
1009 not used hint:
1010 duplication hint:
1011 error hint:
1012
1013 /*+
1014 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
1015 Leading(b1t2 b1t3 b1t4 b1t1)
1016 Leading(b2t3 b2t4 b2t1 b2t2)
1017 MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4)
1018 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
1019 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
1020 Rows(c2 c1 #1)Rows(c2 c1 bmt1 #1)Rows(c2 c1 bmt1 bmt2 #1)Rows(c2 c1 bmt1 bmt2 bmt3 #1)Rows(c2 c1 bmt1 bmt2 bmt3 bmt4 #1)
1021 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
1022 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
1023 */
1024 EXPLAIN
1025 WITH c1 (c1) AS (
1026 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1027 )
1028 , c2 (c1) AS (
1029 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
1030 )
1031 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
1032 , c1, c2
1033                                                                         WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
1034 AND bmt1.c1 = c1.c1
1035 AND bmt1.c1 = c2.c1
1036 ;
1037 LOG:  pg_hint_plan:
1038 used hint:
1039 MergeJoin(b1t2 b1t3)
1040 MergeJoin(b2t3 b2t4)
1041 MergeJoin(c1 c2)
1042 HashJoin(b1t2 b1t3 b1t4)
1043 HashJoin(b2t1 b2t3 b2t4)
1044 HashJoin(bmt1 c1 c2)
1045 NestLoop(b1t1 b1t2 b1t3 b1t4)
1046 NestLoop(b2t1 b2t2 b2t3 b2t4)
1047 NestLoop(bmt1 bmt2 c1 c2)
1048 MergeJoin(bmt1 bmt2 bmt3 c1 c2)
1049 HashJoin(bmt1 bmt2 bmt3 bmt4 c1 c2)
1050 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
1051 Leading(b1t2 b1t3 b1t4 b1t1)
1052 Leading(b2t3 b2t4 b2t1 b2t2)
1053 Rows(b1t2 b1t3 #1)
1054 Rows(b2t3 b2t4 #1)
1055 Rows(c1 c2 #1)
1056 Rows(b1t2 b1t3 b1t4 #1)
1057 Rows(b2t1 b2t3 b2t4 #1)
1058 Rows(bmt1 c1 c2 #1)
1059 Rows(b1t1 b1t2 b1t3 b1t4 #1)
1060 Rows(b2t1 b2t2 b2t3 b2t4 #1)
1061 Rows(bmt1 bmt2 c1 c2 #1)
1062 Rows(bmt1 bmt2 bmt3 c1 c2 #1)
1063 Rows(bmt1 bmt2 bmt3 bmt4 c1 c2 #1)
1064 not used hint:
1065 duplication hint:
1066 error hint:
1067
1068 \o
1069 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-7.out.log > results/R_2-1-7.out
1070 \! diff expected/R_2-1-7.out results/R_2-1-7.out
1071 -- No. R-2-1-8
1072 \o results/R_2-1-8.out.log
1073 /*+
1074 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1075 Leading(b1t2 b1t3 b1t4 b1t1)
1076 Leading(b2t3 b2t4 b2t1 b2t2)
1077 Leading(b3t4 b3t1 b3t2 b3t3)
1078 MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1079 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
1080 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
1081 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
1082 */
1083 EXPLAIN
1084 WITH c1 (c1) AS (
1085 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1086 )
1087 , c2 (c1) AS (
1088 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
1089 )
1090 , c3 (c1) AS (
1091 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
1092 )
1093 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
1094 , c1, c2, c3
1095                                                                         WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
1096 AND bmt1.c1 = c1.c1
1097 AND bmt1.c1 = c2.c1
1098 AND bmt1.c1 = c3.c1
1099 ;
1100 LOG:  pg_hint_plan:
1101 used hint:
1102 MergeJoin(b1t2 b1t3)
1103 MergeJoin(b2t3 b2t4)
1104 MergeJoin(b3t1 b3t4)
1105 MergeJoin(c2 c3)
1106 HashJoin(b1t2 b1t3 b1t4)
1107 HashJoin(b2t1 b2t3 b2t4)
1108 HashJoin(b3t1 b3t2 b3t4)
1109 HashJoin(c1 c2 c3)
1110 NestLoop(b1t1 b1t2 b1t3 b1t4)
1111 NestLoop(b2t1 b2t2 b2t3 b2t4)
1112 NestLoop(b3t1 b3t2 b3t3 b3t4)
1113 NestLoop(bmt1 c1 c2 c3)
1114 MergeJoin(bmt1 bmt2 c1 c2 c3)
1115 HashJoin(bmt1 bmt2 bmt3 c1 c2 c3)
1116 NestLoop(bmt1 bmt2 bmt3 bmt4 c1 c2 c3)
1117 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1118 Leading(b1t2 b1t3 b1t4 b1t1)
1119 Leading(b2t3 b2t4 b2t1 b2t2)
1120 Leading(b3t4 b3t1 b3t2 b3t3)
1121 not used hint:
1122 duplication hint:
1123 error hint:
1124
1125 /*+
1126 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1127 Leading(b1t2 b1t3 b1t4 b1t1)
1128 Leading(b2t3 b2t4 b2t1 b2t2)
1129 Leading(b3t4 b3t1 b3t2 b3t3)
1130 MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1131 MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
1132 MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
1133 MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
1134 Rows(c3 c2 #1)Rows(c3 c2 c1 #1)Rows(c3 c2 c1 bmt1 #1)Rows(c3 c2 c1 bmt1 bmt2 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 #1)
1135 Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1)
1136 Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1)
1137 Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1)
1138 */
1139 EXPLAIN
1140 WITH c1 (c1) AS (
1141 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1142 )
1143 , c2 (c1) AS (
1144 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
1145 )
1146 , c3 (c1) AS (
1147 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
1148 )
1149 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
1150 , c1, c2, c3
1151                                                                         WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
1152 AND bmt1.c1 = c1.c1
1153 AND bmt1.c1 = c2.c1
1154 AND bmt1.c1 = c3.c1
1155 ;
1156 LOG:  pg_hint_plan:
1157 used hint:
1158 MergeJoin(b1t2 b1t3)
1159 MergeJoin(b2t3 b2t4)
1160 MergeJoin(b3t1 b3t4)
1161 MergeJoin(c2 c3)
1162 HashJoin(b1t2 b1t3 b1t4)
1163 HashJoin(b2t1 b2t3 b2t4)
1164 HashJoin(b3t1 b3t2 b3t4)
1165 HashJoin(c1 c2 c3)
1166 NestLoop(b1t1 b1t2 b1t3 b1t4)
1167 NestLoop(b2t1 b2t2 b2t3 b2t4)
1168 NestLoop(b3t1 b3t2 b3t3 b3t4)
1169 NestLoop(bmt1 c1 c2 c3)
1170 MergeJoin(bmt1 bmt2 c1 c2 c3)
1171 HashJoin(bmt1 bmt2 bmt3 c1 c2 c3)
1172 NestLoop(bmt1 bmt2 bmt3 bmt4 c1 c2 c3)
1173 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
1174 Leading(b1t2 b1t3 b1t4 b1t1)
1175 Leading(b2t3 b2t4 b2t1 b2t2)
1176 Leading(b3t4 b3t1 b3t2 b3t3)
1177 Rows(b1t2 b1t3 #1)
1178 Rows(b2t3 b2t4 #1)
1179 Rows(b3t1 b3t4 #1)
1180 Rows(c2 c3 #1)
1181 Rows(b1t2 b1t3 b1t4 #1)
1182 Rows(b2t1 b2t3 b2t4 #1)
1183 Rows(b3t1 b3t2 b3t4 #1)
1184 Rows(c1 c2 c3 #1)
1185 Rows(b1t1 b1t2 b1t3 b1t4 #1)
1186 Rows(b2t1 b2t2 b2t3 b2t4 #1)
1187 Rows(b3t1 b3t2 b3t3 b3t4 #1)
1188 Rows(bmt1 c1 c2 c3 #1)
1189 Rows(bmt1 bmt2 c1 c2 c3 #1)
1190 Rows(bmt1 bmt2 bmt3 c1 c2 c3 #1)
1191 Rows(bmt1 bmt2 bmt3 bmt4 c1 c2 c3 #1)
1192 not used hint:
1193 duplication hint:
1194 error hint:
1195
1196 \o
1197 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-8.out.log > results/R_2-1-8.out
1198 \! diff expected/R_2-1-8.out results/R_2-1-8.out
1199 ----
1200 ---- No. R-2-2 the number of the tables per quiry block
1201 ----
1202 -- No. R-2-2-1
1203 \o results/R_2-2-1.out.log
1204 /*+
1205 Leading(c1 bmt1)
1206 */
1207 EXPLAIN
1208 WITH c1 (c1) AS (
1209 SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
1210 )
1211 SELECT bmt1.c1, (
1212 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
1213 )
1214                     FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
1215 AND bmt1.c1 = c1.c1
1216 AND bmt1.c1 <> (
1217 SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
1218 )
1219 ;
1220 LOG:  pg_hint_plan:
1221 used hint:
1222 Leading(c1 bmt1)
1223 not used hint:
1224 duplication hint:
1225 error hint:
1226
1227 /*+
1228 Leading(c1 bmt1)
1229 Rows(bmt1 c1 #1)
1230 Rows(b1t1 c1 #1)
1231 Rows(b2t1 c1 #1)
1232 Rows(b3t1 c1 #1)
1233 */
1234 EXPLAIN
1235 WITH c1 (c1) AS (
1236 SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
1237 )
1238 SELECT bmt1.c1, (
1239 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
1240 )
1241                     FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
1242 AND bmt1.c1 = c1.c1
1243 AND bmt1.c1 <> (
1244 SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
1245 )
1246 ;
1247 LOG:  pg_hint_plan:
1248 used hint:
1249 Leading(c1 bmt1)
1250 Rows(bmt1 c1 #1)
1251 not used hint:
1252 Rows(b1t1 c1 #1)
1253 Rows(b2t1 c1 #1)
1254 Rows(b3t1 c1 #1)
1255 duplication hint:
1256 error hint:
1257
1258 \o
1259 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-1.out.log > results/R_2-2-1.out
1260 \! diff expected/R_2-2-1.out results/R_2-2-1.out
1261 -- No. R-2-2-2
1262 \o results/R_2-2-2.out.log
1263 /*+
1264 Leading(c1 bmt2 bmt1)
1265 Leading(b1t2 b1t1)
1266 Leading(b2t2 b2t1)
1267 Leading(b3t2 b3t1)
1268 MergeJoin(c1 bmt2)
1269 HashJoin(c1 bmt1 bmt2)
1270 MergeJoin(b1t1 b1t2)
1271 MergeJoin(b2t1 b2t2)
1272 MergeJoin(b3t1 b3t2)
1273 */
1274 EXPLAIN
1275 WITH c1 (c1) AS (
1276 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
1277 )
1278 SELECT bmt1.c1, (
1279 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
1280 )
1281                     FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
1282 AND bmt1.c1 = c1.c1
1283 AND bmt1.c1 <> (
1284 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
1285 )
1286 ;
1287 LOG:  pg_hint_plan:
1288 used hint:
1289 MergeJoin(b1t1 b1t2)
1290 MergeJoin(b2t1 b2t2)
1291 MergeJoin(b3t1 b3t2)
1292 MergeJoin(bmt2 c1)
1293 HashJoin(bmt1 bmt2 c1)
1294 Leading(c1 bmt2 bmt1)
1295 Leading(b1t2 b1t1)
1296 Leading(b2t2 b2t1)
1297 Leading(b3t2 b3t1)
1298 not used hint:
1299 duplication hint:
1300 error hint:
1301
1302 /*+
1303 Leading(c1 bmt2 bmt1)
1304 Leading(b1t2 b1t1)
1305 Leading(b2t2 b2t1)
1306 Leading(b3t2 b3t1)
1307 MergeJoin(c1 bmt2)
1308 HashJoin(c1 bmt1 bmt2)
1309 MergeJoin(b1t1 b1t2)
1310 MergeJoin(b2t1 b2t2)
1311 MergeJoin(b3t1 b3t2)
1312 Rows(c1 bmt2 #1)
1313 Rows(c1 bmt1 bmt2 #1)
1314 Rows(b1t1 b1t2 #1)
1315 Rows(b2t1 b2t2 #1)
1316 Rows(b3t1 b3t2 #1)
1317 */
1318 EXPLAIN
1319 WITH c1 (c1) AS (
1320 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
1321 )
1322 SELECT bmt1.c1, (
1323 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
1324 )
1325                     FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
1326 AND bmt1.c1 = c1.c1
1327 AND bmt1.c1 <> (
1328 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
1329 )
1330 ;
1331 LOG:  pg_hint_plan:
1332 used hint:
1333 MergeJoin(b1t1 b1t2)
1334 MergeJoin(b2t1 b2t2)
1335 MergeJoin(b3t1 b3t2)
1336 MergeJoin(bmt2 c1)
1337 HashJoin(bmt1 bmt2 c1)
1338 Leading(c1 bmt2 bmt1)
1339 Leading(b1t2 b1t1)
1340 Leading(b2t2 b2t1)
1341 Leading(b3t2 b3t1)
1342 Rows(b1t1 b1t2 #1)
1343 Rows(b2t1 b2t2 #1)
1344 Rows(b3t1 b3t2 #1)
1345 Rows(bmt2 c1 #1)
1346 Rows(bmt1 bmt2 c1 #1)
1347 not used hint:
1348 duplication hint:
1349 error hint:
1350
1351 \o
1352 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-2.out.log > results/R_2-2-2.out
1353 \! diff expected/R_2-2-2.out results/R_2-2-2.out
1354 -- No. R-2-2-3
1355 \o results/R_2-2-3.out.log
1356 /*+
1357 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1358 Leading(b1t4 b1t3 b1t2 b1t1)
1359 Leading(b2t4 b2t3 b2t2 b2t1)
1360 Leading(b3t4 b3t3 b3t2 b3t1)
1361 MergeJoin(c1 bmt4)
1362 HashJoin(c1 bmt4 bmt3)
1363 NestLoop(c1 bmt4 bmt3 bmt2)
1364 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
1365 HashJoin(b1t4 b1t3)
1366 NestLoop(b1t4 b1t3 b1t2)
1367 MergeJoin(b1t4 b1t3 b1t2 b1t1)
1368 HashJoin(b2t4 b2t3)
1369 NestLoop(b2t4 b2t3 b2t2)
1370 MergeJoin(b2t4 b2t3 b2t2 b2t1)
1371 HashJoin(b3t4 b3t3)
1372 NestLoop(b3t4 b3t3 b3t2)
1373 MergeJoin(b3t4 b3t3 b3t2 b3t1)
1374 */
1375 EXPLAIN
1376 WITH c1 (c1) AS (
1377 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1378 )
1379 SELECT bmt1.c1, (
1380 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
1381 )
1382                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
1383 AND bmt1.c1 <> (
1384 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
1385 )
1386 ;
1387 LOG:  pg_hint_plan:
1388 used hint:
1389 HashJoin(b1t3 b1t4)
1390 HashJoin(b2t3 b2t4)
1391 HashJoin(b3t3 b3t4)
1392 MergeJoin(bmt4 c1)
1393 NestLoop(b1t2 b1t3 b1t4)
1394 NestLoop(b2t2 b2t3 b2t4)
1395 NestLoop(b3t2 b3t3 b3t4)
1396 HashJoin(bmt3 bmt4 c1)
1397 MergeJoin(b1t1 b1t2 b1t3 b1t4)
1398 MergeJoin(b2t1 b2t2 b2t3 b2t4)
1399 MergeJoin(b3t1 b3t2 b3t3 b3t4)
1400 NestLoop(bmt2 bmt3 bmt4 c1)
1401 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
1402 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1403 Leading(b1t4 b1t3 b1t2 b1t1)
1404 Leading(b2t4 b2t3 b2t2 b2t1)
1405 Leading(b3t4 b3t3 b3t2 b3t1)
1406 not used hint:
1407 duplication hint:
1408 error hint:
1409
1410 /*+
1411 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1412 Leading(b1t4 b1t3 b1t2 b1t1)
1413 Leading(b2t4 b2t3 b2t2 b2t1)
1414 Leading(b3t4 b3t3 b3t2 b3t1)
1415 MergeJoin(c1 bmt4)
1416 HashJoin(c1 bmt4 bmt3)
1417 NestLoop(c1 bmt4 bmt3 bmt2)
1418 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
1419 HashJoin(b1t4 b1t3)
1420 NestLoop(b1t4 b1t3 b1t2)
1421 MergeJoin(b1t4 b1t3 b1t2 b1t1)
1422 HashJoin(b2t4 b2t3)
1423 NestLoop(b2t4 b2t3 b2t2)
1424 MergeJoin(b2t4 b2t3 b2t2 b2t1)
1425 HashJoin(b3t4 b3t3)
1426 NestLoop(b3t4 b3t3 b3t2)
1427 MergeJoin(b3t4 b3t3 b3t2 b3t1)
1428 Rows(c1 bmt4 #1)
1429 Rows(c1 bmt4 bmt3 #1)
1430 Rows(c1 bmt4 bmt3 bmt2 #1)
1431 Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
1432 Rows(b1t4 b1t3 #1)
1433 Rows(b1t4 b1t3 b1t2 #1)
1434 Rows(b1t4 b1t3 b1t2 b1t1 #1)
1435 Rows(b2t4 b2t3 #1)
1436 Rows(b2t4 b2t3 b2t2 #1)
1437 Rows(b2t4 b2t3 b2t2 b2t1 #1)
1438 Rows(b3t4 b3t3 #1)
1439 Rows(b3t4 b3t3 b3t2 #1)
1440 Rows(b3t4 b3t3 b3t2 b3t1 #1)
1441 */
1442 EXPLAIN
1443 WITH c1 (c1) AS (
1444 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1445 )
1446 SELECT bmt1.c1, (
1447 SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
1448 )
1449                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
1450 AND bmt1.c1 <> (
1451 SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
1452 )
1453 ;
1454 LOG:  pg_hint_plan:
1455 used hint:
1456 HashJoin(b1t3 b1t4)
1457 HashJoin(b2t3 b2t4)
1458 HashJoin(b3t3 b3t4)
1459 MergeJoin(bmt4 c1)
1460 NestLoop(b1t2 b1t3 b1t4)
1461 NestLoop(b2t2 b2t3 b2t4)
1462 NestLoop(b3t2 b3t3 b3t4)
1463 HashJoin(bmt3 bmt4 c1)
1464 MergeJoin(b1t1 b1t2 b1t3 b1t4)
1465 MergeJoin(b2t1 b2t2 b2t3 b2t4)
1466 MergeJoin(b3t1 b3t2 b3t3 b3t4)
1467 NestLoop(bmt2 bmt3 bmt4 c1)
1468 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
1469 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1470 Leading(b1t4 b1t3 b1t2 b1t1)
1471 Leading(b2t4 b2t3 b2t2 b2t1)
1472 Leading(b3t4 b3t3 b3t2 b3t1)
1473 Rows(b1t3 b1t4 #1)
1474 Rows(b2t3 b2t4 #1)
1475 Rows(b3t3 b3t4 #1)
1476 Rows(bmt4 c1 #1)
1477 Rows(b1t2 b1t3 b1t4 #1)
1478 Rows(b2t2 b2t3 b2t4 #1)
1479 Rows(b3t2 b3t3 b3t4 #1)
1480 Rows(bmt3 bmt4 c1 #1)
1481 Rows(b1t1 b1t2 b1t3 b1t4 #1)
1482 Rows(b2t1 b2t2 b2t3 b2t4 #1)
1483 Rows(b3t1 b3t2 b3t3 b3t4 #1)
1484 Rows(bmt2 bmt3 bmt4 c1 #1)
1485 Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
1486 not used hint:
1487 duplication hint:
1488 error hint:
1489
1490 \o
1491 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-3.out.log > results/R_2-2-3.out
1492 \! diff expected/R_2-2-3.out results/R_2-2-3.out
1493 -- No. R-2-2-4
1494 \o results/R_2-2-4.out.log
1495 /*+
1496 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1497 Leading(b1t4 b1t3 b1t2 b1t1)
1498 MergeJoin(c1 bmt4)
1499 HashJoin(c1 bmt4 bmt3)
1500 NestLoop(c1 bmt4 bmt3 bmt2)
1501 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
1502 MergeJoin(b1t4 b1t3)
1503 HashJoin(b1t4 b1t3 b1t2)
1504 NestLoop(b1t4 b1t3 b1t2 b1t1)
1505 */
1506 EXPLAIN
1507 WITH c1 (c1) AS (
1508 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1509 )
1510 SELECT bmt1.c1, (
1511 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
1512 )
1513                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
1514 AND bmt1.c1 <> (
1515 SELECT b3t1.c1 FROM s1.t1 b3t1
1516 )
1517 ;
1518 LOG:  pg_hint_plan:
1519 used hint:
1520 MergeJoin(b1t3 b1t4)
1521 MergeJoin(bmt4 c1)
1522 HashJoin(b1t2 b1t3 b1t4)
1523 HashJoin(bmt3 bmt4 c1)
1524 NestLoop(b1t1 b1t2 b1t3 b1t4)
1525 NestLoop(bmt2 bmt3 bmt4 c1)
1526 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
1527 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1528 Leading(b1t4 b1t3 b1t2 b1t1)
1529 not used hint:
1530 duplication hint:
1531 error hint:
1532
1533 /*+
1534 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1535 Leading(b1t4 b1t3 b1t2 b1t1)
1536 MergeJoin(c1 bmt4)
1537 HashJoin(c1 bmt4 bmt3)
1538 NestLoop(c1 bmt4 bmt3 bmt2)
1539 MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
1540 MergeJoin(b1t4 b1t3)
1541 HashJoin(b1t4 b1t3 b1t2)
1542 NestLoop(b1t4 b1t3 b1t2 b1t1)
1543 Rows(c1 bmt4 #1)
1544 Rows(c1 bmt4 bmt3 #1)
1545 Rows(c1 bmt4 bmt3 bmt2 #1)
1546 Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
1547 Rows(b1t4 b1t3 #1)
1548 Rows(b1t4 b1t3 b1t2 #1)
1549 Rows(b1t4 b1t3 b1t2 b1t1 #1)
1550 */
1551 EXPLAIN
1552 WITH c1 (c1) AS (
1553 SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
1554 )
1555 SELECT bmt1.c1, (
1556 SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
1557 )
1558                     FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
1559 AND bmt1.c1 <> (
1560 SELECT b3t1.c1 FROM s1.t1 b3t1
1561 )
1562 ;
1563 LOG:  pg_hint_plan:
1564 used hint:
1565 MergeJoin(b1t3 b1t4)
1566 MergeJoin(bmt4 c1)
1567 HashJoin(b1t2 b1t3 b1t4)
1568 HashJoin(bmt3 bmt4 c1)
1569 NestLoop(b1t1 b1t2 b1t3 b1t4)
1570 NestLoop(bmt2 bmt3 bmt4 c1)
1571 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
1572 Leading(c1 bmt4 bmt3 bmt2 bmt1)
1573 Leading(b1t4 b1t3 b1t2 b1t1)
1574 Rows(b1t3 b1t4 #1)
1575 Rows(bmt4 c1 #1)
1576 Rows(b1t2 b1t3 b1t4 #1)
1577 Rows(bmt3 bmt4 c1 #1)
1578 Rows(b1t1 b1t2 b1t3 b1t4 #1)
1579 Rows(bmt2 bmt3 bmt4 c1 #1)
1580 Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
1581 not used hint:
1582 duplication hint:
1583 error hint:
1584
1585 \o
1586 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
1587 \! diff expected/R_2-2-4.out results/R_2-2-4.out
1588 ----
1589 ---- No. R-2-3 RULE or VIEW
1590 ----
1591 -- No. R-2-3-1
1592 \o results/R_2-3-1.out.log
1593 /*+
1594 Leading(r1 t1 t2 t3 t4)
1595 */
1596 EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
1597 LOG:  pg_hint_plan:
1598 used hint:
1599 Leading(r1 t1 t2 t3 t4)
1600 not used hint:
1601 duplication hint:
1602 error hint:
1603
1604 /*+
1605 Leading(r1 t1 t2 t3 t4)
1606 Rows(r1 t1 t2 t3 t4 #2)
1607 Rows(r1 t1 t2 t3 #2)
1608 Rows(r1 t1 t2 #2)
1609 Rows(r1 t1 #2)
1610 */
1611 EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
1612 LOG:  pg_hint_plan:
1613 used hint:
1614 Leading(r1 t1 t2 t3 t4)
1615 Rows(r1 t1 #2)
1616 Rows(r1 t1 t2 #2)
1617 Rows(r1 t1 t2 t3 #2)
1618 Rows(r1 t1 t2 t3 t4 #2)
1619 not used hint:
1620 duplication hint:
1621 error hint:
1622
1623 /*+
1624 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1625 */
1626 EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
1627 LOG:  pg_hint_plan:
1628 used hint:
1629 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1630 not used hint:
1631 duplication hint:
1632 error hint:
1633
1634 /*+
1635 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1636 Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
1637 Rows(r1_ b1t1 b1t2 b1t3 #2)
1638 Rows(r1_ b1t1 b1t2 #2)
1639 Rows(r1_ b1t1 #2)
1640 */
1641 EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
1642 LOG:  pg_hint_plan:
1643 used hint:
1644 Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1645 Rows(b1t1 r1_ #2)
1646 Rows(b1t1 b1t2 r1_ #2)
1647 Rows(b1t1 b1t2 b1t3 r1_ #2)
1648 Rows(b1t1 b1t2 b1t3 b1t4 r1_ #2)
1649 not used hint:
1650 duplication hint:
1651 error hint:
1652
1653 \o
1654 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
1655 \! diff expected/R_2-3-1.out results/R_2-3-1.out
1656 -- No. R-2-3-2
1657 \o results/R_2-3-2.out.log
1658 /*+
1659 Leading(r2 t1 t2 t3 t4)
1660 */
1661 EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
1662 LOG:  pg_hint_plan:
1663 used hint:
1664 Leading(r2 t1 t2 t3 t4)
1665 not used hint:
1666 duplication hint:
1667 error hint:
1668
1669 LOG:  pg_hint_plan:
1670 used hint:
1671 Leading(r2 t1 t2 t3 t4)
1672 not used hint:
1673 duplication hint:
1674 error hint:
1675
1676 /*+
1677 Leading(r2 t1 t2 t3 t4)
1678 Rows(r2 t1 t2 t3 t4 #2)
1679 Rows(r2 t1 t2 t3 #2)
1680 Rows(r2 t1 t2 #2)
1681 Rows(r2 t1 #2)
1682 */
1683 EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
1684 LOG:  pg_hint_plan:
1685 used hint:
1686 Leading(r2 t1 t2 t3 t4)
1687 Rows(r2 t1 #2)
1688 Rows(r2 t1 t2 #2)
1689 Rows(r2 t1 t2 t3 #2)
1690 Rows(r2 t1 t2 t3 t4 #2)
1691 not used hint:
1692 duplication hint:
1693 error hint:
1694
1695 LOG:  pg_hint_plan:
1696 used hint:
1697 Leading(r2 t1 t2 t3 t4)
1698 Rows(r2 t1 #2)
1699 Rows(r2 t1 t2 #2)
1700 Rows(r2 t1 t2 t3 #2)
1701 Rows(r2 t1 t2 t3 t4 #2)
1702 not used hint:
1703 duplication hint:
1704 error hint:
1705
1706 /*+
1707 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1708 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1709 */
1710 EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
1711 LOG:  pg_hint_plan:
1712 used hint:
1713 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1714 not used hint:
1715 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1716 duplication hint:
1717 error hint:
1718
1719 LOG:  pg_hint_plan:
1720 used hint:
1721 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1722 not used hint:
1723 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1724 duplication hint:
1725 error hint:
1726
1727 /*+
1728 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1729 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1730 Rows(r2_ b1t1 #2)
1731 Rows(r2_ b1t1 b1t2 #2)
1732 Rows(r2_ b1t1 b1t2 b1t3 #2)
1733 Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
1734 Rows(r2_ b2t1 #2)
1735 Rows(r2_ b2t1 b2t2 #2)
1736 Rows(r2_ b2t1 b2t2 b2t3  #2)
1737 Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
1738 */
1739 EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
1740 LOG:  pg_hint_plan:
1741 used hint:
1742 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1743 Rows(b1t1 r2_ #2)
1744 Rows(b1t1 b1t2 r2_ #2)
1745 Rows(b1t1 b1t2 b1t3 r2_ #2)
1746 Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
1747 not used hint:
1748 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1749 Rows(b2t1 r2_ #2)
1750 Rows(b2t1 b2t2 r2_ #2)
1751 Rows(b2t1 b2t2 b2t3 r2_ #2)
1752 Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
1753 duplication hint:
1754 error hint:
1755
1756 LOG:  pg_hint_plan:
1757 used hint:
1758 Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1759 Rows(b2t1 r2_ #2)
1760 Rows(b2t1 b2t2 r2_ #2)
1761 Rows(b2t1 b2t2 b2t3 r2_ #2)
1762 Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
1763 not used hint:
1764 Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1765 Rows(b1t1 r2_ #2)
1766 Rows(b1t1 b1t2 r2_ #2)
1767 Rows(b1t1 b1t2 b1t3 r2_ #2)
1768 Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
1769 duplication hint:
1770 error hint:
1771
1772 \o
1773 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
1774 \! diff expected/R_2-3-2.out results/R_2-3-2.out
1775 -- No. R-2-3-3
1776 \o results/R_2-3-3.out.log
1777 /*+
1778 Leading(r3 t1 t2 t3 t4)
1779 */
1780 EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
1781 LOG:  pg_hint_plan:
1782 used hint:
1783 Leading(r3 t1 t2 t3 t4)
1784 not used hint:
1785 duplication hint:
1786 error hint:
1787
1788 LOG:  pg_hint_plan:
1789 used hint:
1790 Leading(r3 t1 t2 t3 t4)
1791 not used hint:
1792 duplication hint:
1793 error hint:
1794
1795 LOG:  pg_hint_plan:
1796 used hint:
1797 Leading(r3 t1 t2 t3 t4)
1798 not used hint:
1799 duplication hint:
1800 error hint:
1801
1802 /*+
1803 Leading(r3 t1 t2 t3 t4)
1804 Rows(r3 t1 t2 t3 t4 #2)
1805 Rows(r3 t1 t2 t3 #2)
1806 Rows(r3 t1 t2 #2)
1807 Rows(r3 t1 #2)
1808 */
1809 EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
1810 LOG:  pg_hint_plan:
1811 used hint:
1812 Leading(r3 t1 t2 t3 t4)
1813 Rows(r3 t1 #2)
1814 Rows(r3 t1 t2 #2)
1815 Rows(r3 t1 t2 t3 #2)
1816 Rows(r3 t1 t2 t3 t4 #2)
1817 not used hint:
1818 duplication hint:
1819 error hint:
1820
1821 LOG:  pg_hint_plan:
1822 used hint:
1823 Leading(r3 t1 t2 t3 t4)
1824 Rows(r3 t1 #2)
1825 Rows(r3 t1 t2 #2)
1826 Rows(r3 t1 t2 t3 #2)
1827 Rows(r3 t1 t2 t3 t4 #2)
1828 not used hint:
1829 duplication hint:
1830 error hint:
1831
1832 LOG:  pg_hint_plan:
1833 used hint:
1834 Leading(r3 t1 t2 t3 t4)
1835 Rows(r3 t1 #2)
1836 Rows(r3 t1 t2 #2)
1837 Rows(r3 t1 t2 t3 #2)
1838 Rows(r3 t1 t2 t3 t4 #2)
1839 not used hint:
1840 duplication hint:
1841 error hint:
1842
1843 /*+
1844 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1845 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1846 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1847 */
1848 EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
1849 LOG:  pg_hint_plan:
1850 used hint:
1851 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1852 not used hint:
1853 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1854 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1855 duplication hint:
1856 error hint:
1857
1858 LOG:  pg_hint_plan:
1859 used hint:
1860 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1861 not used hint:
1862 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1863 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1864 duplication hint:
1865 error hint:
1866
1867 LOG:  pg_hint_plan:
1868 used hint:
1869 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1870 not used hint:
1871 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1872 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1873 duplication hint:
1874 error hint:
1875
1876 /*+
1877 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1878 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1879 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1880 Rows(r3_ b1t1 #2)
1881 Rows(r3_ b1t1 b1t2 #2)
1882 Rows(r3_ b1t1 b1t2 b1t3 #2)
1883 Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
1884 Rows(r3_ b2t1 #2)
1885 Rows(r3_ b2t1 b2t2 #2)
1886 Rows(r3_ b2t1 b2t2 b2t3 #2)
1887 Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
1888 Rows(r3_ b3t1 #2)
1889 Rows(r3_ b3t1 b3t2 #2)
1890 Rows(r3_ b3t1 b3t2 b3t3 #2)
1891 Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
1892 */
1893 EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
1894 LOG:  pg_hint_plan:
1895 used hint:
1896 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1897 Rows(b1t1 r3_ #2)
1898 Rows(b1t1 b1t2 r3_ #2)
1899 Rows(b1t1 b1t2 b1t3 r3_ #2)
1900 Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1901 not used hint:
1902 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1903 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1904 Rows(b2t1 r3_ #2)
1905 Rows(b3t1 r3_ #2)
1906 Rows(b2t1 b2t2 r3_ #2)
1907 Rows(b3t1 b3t2 r3_ #2)
1908 Rows(b2t1 b2t2 b2t3 r3_ #2)
1909 Rows(b3t1 b3t2 b3t3 r3_ #2)
1910 Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1911 Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1912 duplication hint:
1913 error hint:
1914
1915 LOG:  pg_hint_plan:
1916 used hint:
1917 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1918 Rows(b2t1 r3_ #2)
1919 Rows(b2t1 b2t2 r3_ #2)
1920 Rows(b2t1 b2t2 b2t3 r3_ #2)
1921 Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1922 not used hint:
1923 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1924 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1925 Rows(b1t1 r3_ #2)
1926 Rows(b3t1 r3_ #2)
1927 Rows(b1t1 b1t2 r3_ #2)
1928 Rows(b3t1 b3t2 r3_ #2)
1929 Rows(b1t1 b1t2 b1t3 r3_ #2)
1930 Rows(b3t1 b3t2 b3t3 r3_ #2)
1931 Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1932 Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1933 duplication hint:
1934 error hint:
1935
1936 LOG:  pg_hint_plan:
1937 used hint:
1938 Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1939 Rows(b3t1 r3_ #2)
1940 Rows(b3t1 b3t2 r3_ #2)
1941 Rows(b3t1 b3t2 b3t3 r3_ #2)
1942 Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1943 not used hint:
1944 Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1945 Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1946 Rows(b1t1 r3_ #2)
1947 Rows(b2t1 r3_ #2)
1948 Rows(b1t1 b1t2 r3_ #2)
1949 Rows(b2t1 b2t2 r3_ #2)
1950 Rows(b1t1 b1t2 b1t3 r3_ #2)
1951 Rows(b2t1 b2t2 b2t3 r3_ #2)
1952 Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1953 Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1954 duplication hint:
1955 error hint:
1956
1957 \o
1958 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
1959 \! diff expected/R_2-3-3.out results/R_2-3-3.out
1960 -- No. R-2-3-4
1961 \o results/R_2-3-4.out.log
1962 /*+HashJoin(v1t1 v1t1)*/
1963 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
1964 INFO:  hint syntax error at or near "HashJoin(v1t1 v1t1)"
1965 DETAIL:  Relation name "v1t1" is ambiguous.
1966 LOG:  pg_hint_plan:
1967 used hint:
1968 not used hint:
1969 duplication hint:
1970 error hint:
1971 HashJoin(v1t1 v1t1)
1972
1973 /*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
1974 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
1975 INFO:  hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)"
1976 DETAIL:  Relation name "v1t1" is ambiguous.
1977 INFO:  hint syntax error at or near "Rows(v1t1 v1t1 #1)"
1978 DETAIL:  Relation name "v1t1" is ambiguous.
1979 LOG:  pg_hint_plan:
1980 used hint:
1981 not used hint:
1982 duplication hint:
1983 error hint:
1984 HashJoin(v1t1 v1t1)
1985 Rows(v1t1 v1t1 #1)
1986
1987 \o
1988 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
1989 \! diff expected/R_2-3-4.out results/R_2-3-4.out
1990 -- No. R-2-3-5
1991 \o results/R_2-3-5.out.log
1992 /*+NestLoop(v1t1 v1t1_)*/
1993 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
1994 LOG:  pg_hint_plan:
1995 used hint:
1996 NestLoop(v1t1 v1t1_)
1997 not used hint:
1998 duplication hint:
1999 error hint:
2000
2001 /*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
2002 EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
2003 LOG:  pg_hint_plan:
2004 used hint:
2005 NestLoop(v1t1 v1t1_)
2006 Rows(v1t1 v1t1_ #1)
2007 not used hint:
2008 duplication hint:
2009 error hint:
2010
2011 \o
2012 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
2013 \! diff expected/R_2-3-5.out results/R_2-3-5.out
2014 -- No. R-2-3-6
2015 \o results/R_2-3-6.out.log
2016 /*+RowsHashJoin(r4t1 r4t1)*/
2017 EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
2018 INFO:  hint syntax error at or near "RowsHashJoin(r4t1 r4t1)"
2019 DETAIL:  Unrecognized hint keyword "RowsHashJoin".
2020 /*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
2021 EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
2022 INFO:  hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)"
2023 DETAIL:  Unrecognized hint keyword "RowsHashJoin".
2024 \o
2025 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
2026 \! diff expected/R_2-3-6.out results/R_2-3-6.out
2027 -- No. R-2-3-7
2028 \o results/R_2-3-7.out.log
2029 /*+NestLoop(r4t1 r5t1)*/
2030 EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
2031 LOG:  pg_hint_plan:
2032 used hint:
2033 NestLoop(r4t1 r5t1)
2034 not used hint:
2035 duplication hint:
2036 error hint:
2037
2038 /*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
2039 EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
2040 LOG:  pg_hint_plan:
2041 used hint:
2042 NestLoop(r4t1 r5t1)
2043 Rows(r4t1 r5t1 #1)
2044 not used hint:
2045 duplication hint:
2046 error hint:
2047
2048 \o
2049 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
2050 \! diff expected/R_2-3-7.out results/R_2-3-7.out
2051 ----
2052 ---- No. R-2-4 VALUES clause
2053 ----
2054 -- No. R-2-4-1
2055 \o results/R_2-4-1.out.log
2056 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
2057 /*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #2)*/
2058 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
2059 LOG:  pg_hint_plan:
2060 used hint:
2061 not used hint:
2062 Leading(t3 t1 t2)
2063 Rows(t1 t3 #2)
2064 Rows(t1 t2 t3 #2)
2065 duplication hint:
2066 error hint:
2067
2068 /*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
2069 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
2070 LOG:  pg_hint_plan:
2071 used hint:
2072 Leading(*VALUES* t1 t2)
2073 Rows(*VALUES* t1 #2)
2074 Rows(*VALUES* t1 t2 #2)
2075 not used hint:
2076 duplication hint:
2077 error hint:
2078
2079 \o
2080 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-4-1.out.log > results/R_2-4-1.out
2081 \! diff expected/R_2-4-1.out results/R_2-4-1.out
2082 -- No. R-2-4-2
2083 \o results/R_2-4-2.out.log
2084 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
2085 /*+ Leading(t4 t3 t2 t1) Rows(t4 t3 #2) Rows(t4 t3 t2 #2)Rows(t4 t3 t2 t1 #2)*/
2086 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
2087 LOG:  pg_hint_plan:
2088 used hint:
2089 not used hint:
2090 Leading(t4 t3 t2 t1)
2091 Rows(t3 t4 #2)
2092 Rows(t2 t3 t4 #2)
2093 Rows(t1 t2 t3 t4 #2)
2094 duplication hint:
2095 error hint:
2096
2097 /*+ Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)*/
2098 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
2099 INFO:  hint syntax error at or near "Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
2100 DETAIL:  Relation name "*VALUES*" is ambiguous.
2101 INFO:  hint syntax error at or near "Rows(*VALUES* t3 t2 t1 #2)"
2102 DETAIL:  Relation name "*VALUES*" is ambiguous.
2103 INFO:  hint syntax error at or near "Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
2104 DETAIL:  Relation name "*VALUES*" is ambiguous.
2105 LOG:  pg_hint_plan:
2106 used hint:
2107 not used hint:
2108 Rows(t3 t4 #2)
2109 duplication hint:
2110 error hint:
2111 Leading(*VALUES* t3 t2 t1)
2112 Rows(*VALUES* t2 t3 #2)
2113 Rows(*VALUES* t1 t2 t3 #2)
2114
2115 \o
2116 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-4-2.out.log > results/R_2-4-2.out
2117 \! diff expected/R_2-4-2.out results/R_2-4-2.out
2118 ----
2119 ---- No. R-2-5
2120 ----
2121 -- No. R-2-5-1
2122 \o results/R_2-5-1.out.log
2123 EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2124 /*+
2125 Leading(bmt4 bmt3 bmt2 bmt1)
2126 Rows(bmt1 bmt2 bmt3 bmt4 *0.7)
2127 */
2128 EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2129 LOG:  pg_hint_plan:
2130 used hint:
2131 Leading(bmt4 bmt3 bmt2 bmt1)
2132 Rows(bmt1 bmt2 bmt3 bmt4 *0.7)
2133 not used hint:
2134 duplication hint:
2135 error hint:
2136
2137 \o
2138 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-1.out.log > results/R_2-5-1.out
2139 \! diff expected/R_2-5-1.out results/R_2-5-1.out
2140 -- No. R-2-5-2
2141 \o results/R_2-5-2.out.log
2142 EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2143 /*+
2144 Leading(bmt4 bmt3 bmt2 bmt1)
2145 Rows(bmt4 bmt3 *0.6)
2146 */
2147 EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2148 LOG:  pg_hint_plan:
2149 used hint:
2150 Leading(bmt4 bmt3 bmt2 bmt1)
2151 Rows(bmt3 bmt4 *0.6)
2152 not used hint:
2153 duplication hint:
2154 error hint:
2155
2156 \o
2157 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-2.out.log > results/R_2-5-2.out
2158 \! diff expected/R_2-5-2.out results/R_2-5-2.out
2159 -- No. R-2-5-3
2160 \o results/R_2-5-3.out.log
2161 EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2162 /*+
2163 Leading(bmt4 bmt3 bmt2 bmt1)
2164 Rows(bmt4 bmt1 *0.5)
2165 */
2166 EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
2167 LOG:  pg_hint_plan:
2168 used hint:
2169 Leading(bmt4 bmt3 bmt2 bmt1)
2170 Rows(bmt1 bmt4 *0.5)
2171 not used hint:
2172 duplication hint:
2173 error hint:
2174
2175 \o
2176 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-3.out.log > results/R_2-5-3.out
2177 \! diff expected/R_2-5-3.out results/R_2-5-3.out
2178 ----
2179 ---- No. R-3-1 abusolute value
2180 ----
2181 -- No. R-3-1-1
2182 \o results/R_3-1-1.out.log
2183 /*+Rows(t1 t2 #0)*/
2184 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2185 WARNING:  make rows estimation 1 since below 1 : Rows(t1 t2 #0)
2186 LOG:  pg_hint_plan:
2187 used hint:
2188 Rows(t1 t2 #0)
2189 not used hint:
2190 duplication hint:
2191 error hint:
2192
2193 \o
2194 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-1-1.out.log > results/R_3-1-1.out
2195 \! diff expected/R_3-1-1.out results/R_3-1-1.out
2196 -- No. R-3-1-2
2197 \o results/R_3-1-2.out.log
2198 /*+Rows(t1 t2 #5)*/
2199 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2200 LOG:  pg_hint_plan:
2201 used hint:
2202 Rows(t1 t2 #5)
2203 not used hint:
2204 duplication hint:
2205 error hint:
2206
2207 \o
2208 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-1-2.out.log > results/R_3-1-2.out
2209 \! diff expected/R_3-1-2.out results/R_3-1-2.out
2210 ----
2211 ---- No. R-3-2 increase or decrease value
2212 ----
2213 -- No. R-3-2-1
2214 \o results/R_3-2-1.out.log
2215 /*+Rows(t1 t2 +1)*/
2216 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2217 LOG:  pg_hint_plan:
2218 used hint:
2219 Rows(t1 t2 +1)
2220 not used hint:
2221 duplication hint:
2222 error hint:
2223
2224 \o
2225 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-2-1.out.log > results/R_3-2-1.out
2226 \! diff expected/R_3-2-1.out results/R_3-2-1.out
2227 -- No. R-3-2-2
2228 \o results/R_3-2-2.out.log
2229 /*+Rows(t1 t2 -1)*/
2230 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2231 LOG:  pg_hint_plan:
2232 used hint:
2233 Rows(t1 t2 -1)
2234 not used hint:
2235 duplication hint:
2236 error hint:
2237
2238 \o
2239 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-2-2.out.log > results/R_3-2-2.out
2240 \! diff expected/R_3-2-2.out results/R_3-2-2.out
2241 -- No. R-3-2-3
2242 \o results/R_3-2-3.out.log
2243 /*+Rows(t1 t2 -1000)*/
2244 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2245 WARNING:  make rows estimation 1 since below 1 : Rows(t1 t2 -1000)
2246 LOG:  pg_hint_plan:
2247 used hint:
2248 Rows(t1 t2 -1000)
2249 not used hint:
2250 duplication hint:
2251 error hint:
2252
2253 \o
2254 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-2-3.out.log > results/R_3-2-3.out
2255 \! diff expected/R_3-2-3.out results/R_3-2-3.out
2256 ----
2257 ---- No. R-3-3 multiple
2258 ----
2259 -- No. R-3-3-1
2260 \o results/R_3-3-1.out.log
2261 /*+Rows(t1 t2 *0)*/
2262 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2263 WARNING:  make rows estimation 1 since below 1 : Rows(t1 t2 *0)
2264 LOG:  pg_hint_plan:
2265 used hint:
2266 Rows(t1 t2 *0)
2267 not used hint:
2268 duplication hint:
2269 error hint:
2270
2271 \o
2272 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-3-1.out.log > results/R_3-3-1.out
2273 \! diff expected/R_3-3-1.out results/R_3-3-1.out
2274 -- No. R-3-3-2
2275 \o results/R_3-3-2.out.log
2276 /*+Rows(t1 t2 *2)*/
2277 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2278 LOG:  pg_hint_plan:
2279 used hint:
2280 Rows(t1 t2 *2)
2281 not used hint:
2282 duplication hint:
2283 error hint:
2284
2285 \o
2286 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-3-2.out.log > results/R_3-3-2.out
2287 \! diff expected/R_3-3-2.out results/R_3-3-2.out
2288 -- No. R-3-3-3
2289 \o results/R_3-3-3.out.log
2290 /*+Rows(t1 t2 *0.1)*/
2291 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2292 LOG:  pg_hint_plan:
2293 used hint:
2294 Rows(t1 t2 *0.1)
2295 not used hint:
2296 duplication hint:
2297 error hint:
2298
2299 \o
2300 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-3-3.out.log > results/R_3-3-3.out
2301 \! diff expected/R_3-3-3.out results/R_3-3-3.out
2302 ----
2303 ---- No. R-3-4 join inherit tables
2304 ----
2305 -- No. R-3-4-1
2306 \o results/R_3-4-1.out.log
2307 EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
2308 /*+Rows(p1 p2 #1)*/
2309 EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
2310 LOG:  pg_hint_plan:
2311 used hint:
2312 Rows(p1 p2 #1)
2313 not used hint:
2314 duplication hint:
2315 error hint:
2316
2317 \o
2318 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-4-1.out.log > results/R_3-4-1.out
2319 \! diff expected/R_3-4-1.out results/R_3-4-1.out
2320 -- No. R-3-4-2
2321 \o results/R_3-4-2.out.log
2322 EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
2323 /*+Rows(p1c1 p2c1 #1)*/
2324 EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
2325 LOG:  pg_hint_plan:
2326 used hint:
2327 not used hint:
2328 Rows(p1c1 p2c1 #1)
2329 duplication hint:
2330 error hint:
2331
2332 \o
2333 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-4-2.out.log > results/R_3-4-2.out
2334 \! diff expected/R_3-4-2.out results/R_3-4-2.out
2335 ----
2336 ---- No. R-3-5 conflict join method hint
2337 ----
2338 -- No. R-3-5-1
2339 \o results/R_3-5-1.out.log
2340 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2341 /*+Rows(t1 t2 #1)Rows(t1 t2 #1)*/
2342 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2343 INFO:  hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)"
2344 DETAIL:  Conflict rows hint.
2345 LOG:  pg_hint_plan:
2346 used hint:
2347 Rows(t1 t2 #1)
2348 not used hint:
2349 duplication hint:
2350 Rows(t1 t2 #1)
2351 error hint:
2352
2353 \o
2354 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-5-1.out.log > results/R_3-5-1.out
2355 \! diff expected/R_3-5-1.out results/R_3-5-1.out
2356 -- No. R-3-5-2
2357 \o results/R_3-5-2.out.log
2358 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2359 /*+Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)*/
2360 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2361 INFO:  hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)"
2362 DETAIL:  Conflict rows hint.
2363 INFO:  hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)"
2364 DETAIL:  Conflict rows hint.
2365 LOG:  pg_hint_plan:
2366 used hint:
2367 Rows(t1 t2 #1)
2368 not used hint:
2369 duplication hint:
2370 Rows(t1 t2 #1)
2371 Rows(t1 t2 #1)
2372 error hint:
2373
2374 \o
2375 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-5-2.out.log > results/R_3-5-2.out
2376 \! diff expected/R_3-5-2.out results/R_3-5-2.out
2377 -- No. R-3-5-3
2378 \o results/R_3-5-3.out.log
2379 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2380 /*+Rows(t1 t2 #1)Rows(t2 t1 #1)*/
2381 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2382 INFO:  hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)"
2383 DETAIL:  Conflict rows hint.
2384 LOG:  pg_hint_plan:
2385 used hint:
2386 Rows(t1 t2 #1)
2387 not used hint:
2388 duplication hint:
2389 Rows(t1 t2 #1)
2390 error hint:
2391
2392 \o
2393 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-5-3.out.log > results/R_3-5-3.out
2394 \! diff expected/R_3-5-3.out results/R_3-5-3.out
2395 -- No. R-3-5-4
2396 \o results/R_3-5-4.out.log
2397 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2398 /*+Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)*/
2399 EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
2400 INFO:  hint syntax error at or near "Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)"
2401 DETAIL:  Conflict rows hint.
2402 INFO:  hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)"
2403 DETAIL:  Conflict rows hint.
2404 LOG:  pg_hint_plan:
2405 used hint:
2406 Rows(t1 t2 #1)
2407 not used hint:
2408 duplication hint:
2409 Rows(t1 t2 #1)
2410 Rows(t1 t2 #1)
2411 error hint:
2412
2413 \o
2414 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_3-5-4.out.log > results/R_3-5-4.out
2415 \! diff expected/R_3-5-4.out results/R_3-5-4.out