PostgreSQL hint pg_hint_plan 的详细用法

digoal 2021-01-03

1531

作者

digoal

日期

2021-03-27

背景

https://github.com/ossc-db/pg_hint_plan

选择和PostgreSQL版本对应的branch

支持哪些hint

https://github.com/ossc-db/pg_hint_plan/blob/PG13/pg_hint_plan.c

hint的使用格式

```

define BLOCK_COMMENT_START "/*"

define BLOCK_COMMENT_END "*/"

define HINT_COMMENT_KEYWORD "+"

define HINT_START BLOCK_COMMENT_START HINT_COMMENT_KEYWORD

define HINT_END BLOCK_COMMENT_END

```

hint 关键字

```
/ hint keywords /

define HINT_SEQSCAN "SeqScan"

define HINT_INDEXSCAN "IndexScan"

define HINT_INDEXSCANREGEXP "IndexScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_BITMAPSCAN "BitmapScan"

define HINT_BITMAPSCANREGEXP "BitmapScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_TIDSCAN "TidScan"

define HINT_NOSEQSCAN "NoSeqScan"

define HINT_NOINDEXSCAN "NoIndexScan"

define HINT_NOBITMAPSCAN "NoBitmapScan"

define HINT_NOTIDSCAN "NoTidScan"

define HINT_INDEXONLYSCAN "IndexOnlyScan"

define HINT_INDEXONLYSCANREGEXP "IndexOnlyScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_NOINDEXONLYSCAN "NoIndexOnlyScan"

define HINT_PARALLEL "Parallel"

define HINT_NESTLOOP "NestLoop" JOIN方法

define HINT_MERGEJOIN "MergeJoin" JOIN方法

define HINT_HASHJOIN "HashJoin" JOIN方法

define HINT_NONESTLOOP "NoNestLoop"

define HINT_NOMERGEJOIN "NoMergeJoin"

define HINT_NOHASHJOIN "NoHashJoin"

define HINT_LEADING "Leading" JOIN的驱动顺序

define HINT_SET "Set" 设置参数

define HINT_ROWS "Rows" 设置评估为多少行

```

用法

这里有所有hint关键字的例子, 为了方便识别hint模块是否已使用, 明确用了哪些hint, 可以开启一些日志开关

```
SET client_min_messages TO log;
\set SHOW_CONTEXT always
\set VERBOSITY verbose
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print TO on;

/+ hint内容 / SQL ;
```

https://github.com/ossc-db/pg_hint_plan/blob/PG13/expected/pg_hint_plan.out

```
SET search_path TO public;
SET client_min_messages TO log;
\set SHOW_CONTEXT always
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN

Hash Join
Hash Cond: (t2.val = t1.val)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
(5 rows)

LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print TO on;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN

Hash Join
Hash Cond: (t2.val = t1.val)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
(5 rows)

/+ Test (t1 t2) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near "Test (t1 t2) "
DETAIL: Unrecognized hint keyword "Test".
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

SET pg_hint_plan.enable_hint TO off;
/+ Test (t1 t2) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

SET pg_hint_plan.enable_hint TO on;
/Set(enable_indexscan off)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

--+Set(enable_indexscan off)
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+Set(enable_indexscan off) / nest comment / /
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near "/ nest comment / */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;"
DETAIL: Nested block comments are not supported.
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+Set(enable_indexscan off)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

EXPLAIN (COSTS false) /+Set(enable_indexscan off)/
SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+ Set(enable_indexscan off) Set(enable_hashjoin off) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_hashjoin off)
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Sort
Sort Key: t1.id
-> Seq Scan on t1
-> Sort
Sort Key: t2.id
-> Seq Scan on t2
(8 rows)

/+ Set ( enable_indexscan off ) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+
Set
(
enable_indexscan
off
)
/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+ Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
Set(enable_seqscan off)
/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Set(enable_mergejoin off)
Set(enable_nestloop off)
Set(enable_seqscan off)
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Hash
-> Index Scan using t2_pkey on t2
(5 rows)

/+Set(work_mem "1M")/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: invalid value for parameter "work_mem": "1M"
HINT: Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Set(work_mem 1M)

          QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+Set(work_mem "1MB")/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
Set(work_mem 1MB)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+Set(work_mem TO "1MB")/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near "Set(work_mem TO "1MB")"
DETAIL: Set hint requires name and value of GUC parameter.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Set(work_mem TO 1MB)

          QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+SeqScan() / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near " "
DETAIL: SeqScan hint requires a relation.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
SeqScan()

?column?

(1 row)

/+SeqScan(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: SeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
SeqScan(t1 t2)

          QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

/+SeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+SeqScan(t1)IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Index Scan using t2_pkey on t2
(5 rows)

/+BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Seq Scan on t1
-> Bitmap Heap Scan on t2
Recheck Cond: (id = t1.id)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id = t1.id)
(6 rows)

/+BitmapScan(t2)NoSeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Index Scan using t1_pkey on t1
-> Bitmap Heap Scan on t2
Recheck Cond: (id = t1.id)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id = t1.id)
(6 rows)

/+NoIndexScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+NoBitmapScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10;
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
error hint:

     QUERY PLAN

Nested Loop
-> Seq Scan on t1
Filter: (val < 10)
-> Materialize
-> Seq Scan on t4
(5 rows)

/+TidScan(t4)/
EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t4)
not used hint:
duplication hint:
error hint:

              QUERY PLAN

Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Sort
Sort Key: t4.id
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
(7 rows)

/+NoTidScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
(5 rows)

/+ NestLoop() / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near " "
DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
NestLoop()

?column?

(1 row)

/+ NestLoop(x) / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near " "
DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
NestLoop(x)

?column?

(1 row)

/+HashJoin(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
HashJoin(t1 t2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+NestLoop(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t2
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
(4 rows)

/+NoMergeJoin(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
NoMergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+MergeJoin(t1 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t3)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Merge Join
Merge Cond: (t1.val = t3.val)
-> Index Scan using t1_val on t1
-> Sort
Sort Key: t3.val
-> Seq Scan on t3
(6 rows)

/+NestLoop(t1 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
LOG: pg_hint_plan:
used hint:
NestLoop(t1 t3)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Nested Loop
-> Seq Scan on t3
-> Index Scan using t1_val on t1
Index Cond: (val = t3.val)
(4 rows)

/+NoHashJoin(t1 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
LOG: pg_hint_plan:
used hint:
NoHashJoin(t1 t3)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Nested Loop
-> Seq Scan on t3
-> Index Scan using t1_val on t1
Index Cond: (val = t3.val)
(4 rows)

/+MergeJoin(t4 t1 t2 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2 t3 t4)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

/+HashJoin(t3 t4 t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
HashJoin(t1 t2 t3 t4)
not used hint:
duplication hint:
error hint:

                   QUERY PLAN

Hash Join
Hash Cond: (t3.id = t1.id)
-> Seq Scan on t3
-> Hash
-> Merge Join
Merge Cond: (t1.id = t4.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
(13 rows)

/+NestLoop(t2 t3 t4 t1) IndexScan(t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
IndexScan(t3)
NestLoop(t1 t2 t3 t4)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Nested Loop
-> Merge Join
Merge Cond: (t1.id = t4.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t3_pkey on t3
Index Cond: (id = t1.id)
(12 rows)

/+NoNestLoop(t4 t1 t3 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
NoNestLoop(t1 t2 t3 t4)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

/+Leading( /
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: Closing parenthesis is necessary.
QUERY PLAN

/+Leading( )/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
INFO: pg_hint_plan: hint syntax error at or near "Leading( )"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading()

                QUERY PLAN

/+Leading( t3 )/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
INFO: pg_hint_plan: hint syntax error at or near "Leading( t3 )"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading(t3)

                QUERY PLAN

/+Leading( t3 t4 )/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
Leading(t3 t4)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Nested Loop
-> Nested Loop
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t2_pkey on t2
Index Cond: (id = t3.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
(12 rows)

/+Leading(t3 t4 t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
Leading(t3 t4 t1)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Nested Loop
-> Merge Join
Merge Cond: (t3.id = t1.id)
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
(12 rows)

/+Leading(t3 t4 t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
LOG: pg_hint_plan:
used hint:
Leading(t3 t4 t1 t2)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

/+Leading(t3 t4 t1 t2 t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t4 t1 t2 t1)"
DETAIL: Relation name "t1" is duplicated.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading(t3 t4 t1 t2 t1)

                QUERY PLAN

/+Leading(t3 t4 t4)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t4 t4)"
DETAIL: Relation name "t4" is duplicated.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading(t3 t4 t4)

                QUERY PLAN

EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
QUERY PLAN

Nested Loop
-> Values Scan on "VALUES"
-> Index Scan using t1_pkey on t1
Index Cond: (id = "VALUES".column1)
(4 rows)

/+HashJoin(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
not used hint:
HashJoin(t1 t2)
duplication hint:
error hint:

              QUERY PLAN

Nested Loop
-> Values Scan on "VALUES"
-> Index Scan using t1_pkey on t1
Index Cond: (id = "VALUES".column1)
(4 rows)

/+HashJoin(t1 VALUES)/
EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
HashJoin(VALUES t1)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Hash Join
Hash Cond: (t1.id = "VALUES".column1)
-> Seq Scan on t1
-> Hash
-> Values Scan on "VALUES"
(5 rows)

/+HashJoin(t1 VALUES) IndexScan(t1) IndexScan(VALUES)/
EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
HashJoin(VALUES t1)
not used hint:
IndexScan(VALUES)
duplication hint:
error hint:

            QUERY PLAN

Hash Join
Hash Cond: (t1.id = "VALUES".column1)
-> Index Scan using t1_pkey on t1
-> Hash
-> Values Scan on "VALUES"
(5 rows)

-- single table scan hint test
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
QUERY PLAN

Index Only Scan using t1_pkey on t1
Index Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
(14 rows)

/+BitmapScan(v_1)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(v_1)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Index Only Scan using t1_pkey on t1
Index Cond: (id = $3)
InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
InitPlan 3 (returns $3)
-> Result
InitPlan 2 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
(14 rows)

/+BitmapScan(v_2)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(v_2)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Index Only Scan using t1_pkey on t1
Index Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
InitPlan 3 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
(14 rows)

/+BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Bitmap Heap Scan on t1
Recheck Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = $3)
(16 rows)

/+BitmapScan(v_1)BitmapScan(v_2)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(v_1)
BitmapScan(v_2)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Index Only Scan using t1_pkey on t1
Index Cond: (id = $3)
InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
InitPlan 2 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
(14 rows)

/+BitmapScan(v_1)BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(v_1)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Bitmap Heap Scan on t1
Recheck Cond: (id = $3)
InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
InitPlan 3 (returns $3)
-> Result
InitPlan 2 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = $3)
(16 rows)

/+BitmapScan(v_2)BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(v_2)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Bitmap Heap Scan on t1
Recheck Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
InitPlan 3 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = $3)
(16 rows)

/+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(v_1)
BitmapScan(v_2)
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Bitmap Heap Scan on t1
Recheck Cond: (id = $3)
InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
InitPlan 2 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = $3)
(16 rows)

-- full scan hint pattern test
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
QUERY PLAN

Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(3 rows)

/+SeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

               QUERY PLAN

Seq Scan on t1
Filter: ((id < 10) AND (ctid = '(1,1)'::tid))
(2 rows)

/+IndexScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

Index Scan using t1_pkey on t1
Index Cond: (id < 10)
Filter: (ctid = '(1,1)'::tid)
(3 rows)

/+BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Bitmap Heap Scan on t1
Recheck Cond: (id < 10)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
(5 rows)

/+TidScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
not used hint:
duplication hint:
error hint:

        QUERY PLAN

Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(3 rows)

/+NoSeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
not used hint:
duplication hint:
error hint:

        QUERY PLAN

Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(3 rows)

/+NoIndexScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
not used hint:
duplication hint:
error hint:

        QUERY PLAN

Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(3 rows)

/+NoBitmapScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
error hint:

        QUERY PLAN

Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(3 rows)

/+NoTidScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

Index Scan using t1_pkey on t1
Index Cond: (id < 10)
Filter: (ctid = '(1,1)'::tid)
(3 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t2
Recheck Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id = t1.id)
(8 rows)

/+SeqScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+SeqScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Index Scan using t2_pkey on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Index Scan using t1_pkey on t1
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t2
Recheck Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id = t1.id)
(8 rows)

/+IndexScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+IndexScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+BitmapScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t1
Recheck Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = t2.id)
(8 rows)

/+BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t2
Recheck Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id = t1.id)
(8 rows)

            QUERY PLAN

/+BitmapScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t1
Recheck Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = t2.id)
(8 rows)

/+BitmapScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Nested Loop
-> Index Scan using t2_pkey on t2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t1
Recheck Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id = t2.id)
(8 rows)

/+BitmapScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+BitmapScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+BitmapScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+BitmapScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+BitmapScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+BitmapScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+TidScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+TidScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+TidScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+NoSeqScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoSeqScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+NoIndexScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoIndexScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+NoBitmapScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)

/+NoBitmapScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) SeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) IndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
IndexScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Index Scan using t2_pkey on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

/+NoTidScan(t1) TidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
TidScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) NoSeqScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
NoSeqScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) NoIndexScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
NoIndexScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) NoBitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
NoBitmapScan(t2)
not used hint:
duplication hint:
error hint:

           QUERY PLAN

Nested Loop
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

/+NoTidScan(t1) NoTidScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
NoTidScan(t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t2.id)
Filter: (ctid = '(1,1)'::tid)
(6 rows)

-- additional test
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
QUERY PLAN

Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
(8 rows)

/+BitmapScan(t1) BitmapScan(t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(t2)
not used hint:
duplication hint:
error hint:

                   QUERY PLAN

Nested Loop
-> Bitmap Heap Scan on t2
Recheck Cond: (id < 10)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t2_pkey
Index Cond: (id < 10)
-> Bitmap Heap Scan on t1
Recheck Cond: ((id = t2.id) AND (id < 10))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: ((id = t2.id) AND (id < 10))
(11 rows)

-- outer join test
EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
QUERY PLAN

Hash Full Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

/+MergeJoin(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Merge Full Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

-- Cannot work
/+NestLoop(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
NestLoop(t1 t2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Hash Full Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
(5 rows)

-- inheritance tables test
SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
QUERY PLAN

Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(19 rows)

SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
QUERY PLAN

SET constraint_exclusion TO off;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

/+IndexScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(p1)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Append
-> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c2_pkey on p1_c2 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_pkey on p1_c3 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c4_pkey on p1_c4 p1_5
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(28 rows)

/+BitmapScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(p1)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Append
-> Bitmap Heap Scan on p1 p1_1
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1 p1_2
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c2 p1_3
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3 p1_4
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c4 p1_5
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c4_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1_c1 p1_6
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1_c2 p1_7
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3_c1 p1_8
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
(46 rows)

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Append
-> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c2 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c4 p1_5
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1_c1 p1_6
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1_c2 p1_7
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3_c1 p1_8
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3_c2 p1_9
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
(28 rows)

SET constraint_exclusion TO on;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

/+IndexScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(p1)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

/+BitmapScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(p1)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

             QUERY PLAN

SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Sort
Sort Key: p1.id
-> Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(25 rows)

SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN

SET constraint_exclusion TO off;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                                QUERY PLAN

/+IndexScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
IndexScan(p1)
not used hint:
duplication hint:
error hint:

                     QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Merge Append
Sort Key: p1.id
-> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c2_pkey on p1_c2 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_pkey on p1_c3 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c4_pkey on p1_c4 p1_5
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(33 rows)

/+BitmapScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
BitmapScan(p1)
not used hint:
duplication hint:
error hint:

                        QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Sort
Sort Key: p1.id
-> Append
-> Bitmap Heap Scan on p1 p1_1
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1 p1_2
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c2 p1_3
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3 p1_4
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c4 p1_5
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c4_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1_c1 p1_6
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c1_c2 p1_7
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3_c1 p1_8
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c2_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(52 rows)

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

                   QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Sort
Sort Key: p1.id
-> Append
-> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c2 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c4 p1_5
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1_c1 p1_6
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c1_c2 p1_7
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3_c1 p1_8
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Tid Scan on p1_c3_c2 p1_9
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(34 rows)

/+NestLoop(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
NestLoop(p1 t1)
not used hint:
duplication hint:
error hint:

                             QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Materialize
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(24 rows)

/+MergeJoin(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
MergeJoin(p1 t1)
not used hint:
duplication hint:
error hint:

                                QUERY PLAN

/+HashJoin(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
HashJoin(p1 t1)
not used hint:
duplication hint:
error hint:

                             QUERY PLAN

Hash Join
Hash Cond: (p1.id = t1.id)
-> Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(24 rows)

SET constraint_exclusion TO on;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                                QUERY PLAN

                     QUERY PLAN

                        QUERY PLAN

                   QUERY PLAN

                                QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Materialize
-> Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(14 rows)

                                QUERY PLAN

                                QUERY PLAN

Hash Join
Hash Cond: (t1.id = p1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Append
-> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(14 rows)

SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
QUERY PLAN

Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(2 rows)

SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
QUERY PLAN

Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(2 rows)

SET constraint_exclusion TO off;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                       QUERY PLAN

Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(2 rows)

/+IndexScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
IndexScan(p1)
not used hint:
duplication hint:
error hint:

            QUERY PLAN

Index Scan using p1_pkey on p1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(3 rows)

/+BitmapScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
BitmapScan(p1)
not used hint:
duplication hint:
error hint:

               QUERY PLAN

Bitmap Heap Scan on p1
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
(5 rows)

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Tid Scan on p1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
(3 rows)

/+NestLoop(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
NestLoop(p1 t1)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

/+MergeJoin(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
MergeJoin(p1 t1)
not used hint:
duplication hint:
error hint:

                             QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Sort
Sort Key: p1.id
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(8 rows)

/+HashJoin(p1 t1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
HashJoin(p1 t1)
not used hint:
duplication hint:
error hint:

                             QUERY PLAN

Hash Join
Hash Cond: (t1.id = p1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(7 rows)

SET constraint_exclusion TO on;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                       QUERY PLAN

Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(2 rows)

            QUERY PLAN

Index Scan using p1_pkey on p1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(3 rows)

               QUERY PLAN

Bitmap Heap Scan on p1
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
(5 rows)

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Tid Scan on p1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
(3 rows)

                          QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

                             QUERY PLAN

                             QUERY PLAN

Hash Join
Hash Cond: (t1.id = p1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(7 rows)

SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

SET constraint_exclusion TO off;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

/+IndexScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
IndexScan(p1)
not used hint:
duplication hint:
error hint:

               QUERY PLAN

Merge Join
Merge Cond: (p1.id = t1.id)
-> Index Scan using p1_pkey on p1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(7 rows)

/+BitmapScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
BitmapScan(p1)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Bitmap Heap Scan on p1
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_pkey
Index Cond: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(9 rows)

/+TidScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
TidScan(p1)
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Tid Scan on p1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(7 rows)

SET constraint_exclusion TO on;
/+SeqScan(p1)/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
SeqScan(p1)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

               QUERY PLAN

                  QUERY PLAN

             QUERY PLAN

Nested Loop
Join Filter: (p1.id = t1.id)
-> Tid Scan on p1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(7 rows)

-- quote test
/+SeqScan("""t1 ) ")IndexScan("t 2 """)HashJoin("""t1 ) "T3"t 2 """)Leading("""t1 ) "T3"t 2 """)Set(application_name"a a a"" a A")/
EXPLAIN (COSTS false) SELECT * FROM t1 """t1 ) ", t2 "t 2 """, t3 "T3" WHERE """t1 ) ".id = "t 2 """.id AND """t1 ) ".id = "T3".id;
LOG: pg_hint_plan:
used hint:
SeqScan("""t1 ) ")
IndexScan("t 2 """)
HashJoin("""t1 ) " T3 "t 2 """)
Leading("""t1 ) " T3 "t 2 """)
Set(application_name "a a a"" a A")
not used hint:
duplication hint:
error hint:

                   QUERY PLAN

Hash Join
Hash Cond: ("t 2 """.id = """t1 ) ".id)
-> Index Scan using t2_pkey on t2 "t 2 """
-> Hash
-> Hash Join
Hash Cond: ("""t1 ) ".id = "T3".id)
-> Seq Scan on t1 """t1 ) "
-> Hash
-> Seq Scan on t3 "T3"
(9 rows)

-- duplicate hint test
/+SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
INFO: pg_hint_plan: hint syntax error at or near "SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict join method hint.
INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict join method hint.
INFO: pg_hint_plan: hint syntax error at or near "Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict set hint.
INFO: pg_hint_plan: hint syntax error at or near "Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
TidScan(t1)
TidScan(t2)
MergeJoin(t1 t2)
Leading(t2 t1)
Set(enable_mergejoin on)
Set(enable_seqscan on)
not used hint:
duplication hint:
SeqScan(t1)
IndexScan(t1)
BitmapScan(t1)
SeqScan(t2)
IndexScan(t2)
BitmapScan(t2)
HashJoin(t1 t2)
NestLoop(t1 t2)
Leading(t1 t2)
Set(enable_seqscan off)
error hint:

              QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Sort
Sort Key: t1.id
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: t2.id
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
(10 rows)

-- sub query Leading hint test
SET from_collapse_limit TO 100;
SET geqo_threshold TO 100;
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
QUERY PLAN

Nested Loop
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Merge Join
Merge Cond: (t1_2.id = t2_2.id)
-> Index Only Scan using t1_pkey on t1 t1_2
-> Index Only Scan using t2_pkey on t2 t2_2
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Merge Join
Merge Cond: (t1_4.id = t2_4.id)
-> Index Only Scan using t1_pkey on t1 t1_4
-> Index Only Scan using t2_pkey on t2 t2_4
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
Filter: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
Filter: (id = $1)
-> Aggregate
Filter: (max(t1_5.id) = $1)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Merge Join
Merge Cond: (t1_5.id = t2_5.id)
-> Index Only Scan using t1_pkey on t1 t1_5
-> Index Only Scan using t2_pkey on t2 t2_5
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

/+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)/
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
LOG: pg_hint_plan:
used hint:
HashJoin(t1_1 t3_1)
NestLoop(t1_2 t2_2)
MergeJoin(t1_3 t3_3)
NestLoop(t1_4 t2_4)
NestLoop(t1_5 t2_5)
not used hint:
duplication hint:
error hint:

                              QUERY PLAN

Nested Loop
InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_2
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_4
-> Index Only Scan using t1_pkey on t1 t1_4
Index Cond: (id = t2_4.id)
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
Filter: (id = $3)
-> Aggregate
Filter: (max(t1_5.id) = $3)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_5
-> Index Only Scan using t1_pkey on t1 t1_5
Index Cond: (id = t2_5.id)
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

/+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)/
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
LOG: pg_hint_plan:
used hint:
HashJoin(t1_1 t3_1)
NestLoop(t1_2 t2_2)
MergeJoin(t1_3 t3_3)
NestLoop(t1_4 t2_4)
NestLoop(t1_5 t2_5)
not used hint:
Leading(a t1_1 t1_2 t1_4 t1_5)
duplication hint:
error hint:

                              QUERY PLAN

/+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)/
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
LOG: pg_hint_plan:
used hint:
HashJoin(t1_1 t3_1)
NestLoop(t1_2 t2_2)
MergeJoin(t1_3 t3_3)
NestLoop(t1_4 t2_4)
NestLoop(t1_5 t2_5)
not used hint:
Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)
duplication hint:
error hint:

                              QUERY PLAN

/+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)/
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
LOG: pg_hint_plan:
used hint:
Leading(t3_5 t2_5 t1_5)
Leading(t3_2 t2_2 t1_2)
Leading(t3_4 t2_4 t1_4)
Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)
not used hint:
HashJoin(t1_1 t3_1)
NestLoop(t1_2 t2_2)
MergeJoin(t1_3 t3_3)
NestLoop(t1_4 t2_4)
NestLoop(t1_5 t2_5)
duplication hint:
error hint:

                                       QUERY PLAN

Nested Loop
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
-> Merge Join
Merge Cond: (t2_2.id = t3_2.id)
-> Index Only Scan using t2_pkey on t2 t2_2
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Merge Join
Merge Cond: (t2_4.id = t3_4.id)
-> Index Only Scan using t2_pkey on t2 t2_4
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Index Only Scan using t1_pkey on t1 t1_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Seq Scan on t3 t3_3
Filter: (id = $1)
-> Aggregate
Filter: (max(t1_5.id) = $1)
-> Merge Join
Merge Cond: (t2_5.id = t1_5.id)
-> Merge Join
Merge Cond: (t2_5.id = t3_5.id)
-> Index Only Scan using t2_pkey on t2 t2_5
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
-> Index Only Scan using t1_pkey on t1 t1_5
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $1)
(51 rows)

SET from_collapse_limit TO 1;
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
QUERY PLAN

Nested Loop
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Merge Join
Merge Cond: (t1_2.id = t2_2.id)
-> Index Only Scan using t1_pkey on t1 t1_2
-> Index Only Scan using t2_pkey on t2 t2_2
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Merge Join
Merge Cond: (t1_4.id = t2_4.id)
-> Index Only Scan using t1_pkey on t1 t1_4
-> Index Only Scan using t2_pkey on t2 t2_4
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
Filter: (id = $1)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
Filter: (id = $1)
-> Aggregate
Filter: (max(t1_5.id) = $1)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Merge Join
Merge Cond: (t1_5.id = t2_5.id)
-> Index Only Scan using t1_pkey on t1 t1_5
-> Index Only Scan using t2_pkey on t2 t2_5
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

                         QUERY PLAN

Nested Loop
InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_2
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_4
-> Index Only Scan using t1_pkey on t1 t1_4
Index Cond: (id = t2_4.id)
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
Filter: (id = $3)
-> Aggregate
Filter: (max(t1_5.id) = $3)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_5
-> Index Only Scan using t1_pkey on t1 t1_5
Index Cond: (id = t2_5.id)
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

                         QUERY PLAN

Nested Loop
InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_2
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_4
-> Index Only Scan using t1_pkey on t1 t1_4
Index Cond: (id = t2_4.id)
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
Filter: (id = $3)
-> Aggregate
Filter: (max(t1_5.id) = $3)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_5
-> Index Only Scan using t1_pkey on t1 t1_5
Index Cond: (id = t2_5.id)
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

                         QUERY PLAN

Nested Loop
InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_2
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_4
-> Index Only Scan using t1_pkey on t1 t1_4
Index Cond: (id = t2_4.id)
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
Filter: (id = $3)
-> Aggregate
Filter: (max(t1_5.id) = $3)
-> Merge Join
Merge Cond: (t1_5.id = t3_5.id)
-> Nested Loop
-> Index Only Scan using t2_pkey on t2 t2_5
-> Index Only Scan using t1_pkey on t1 t1_5
Index Cond: (id = t2_5.id)
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
(51 rows)

/+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)/
EXPLAIN (COSTS false)
WITH c1_1(id) AS (
SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
)
SELECT t1_1.id, (
SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
);
LOG: pg_hint_plan:
used hint:
MergeJoin(t1_3 t3_3)
Leading(t3_5 t2_5 t1_5)
Leading(t3_2 t2_2 t1_2)
Leading(t3_4 t2_4 t1_4)
Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)
not used hint:
HashJoin(t1_1 t3_1)
NestLoop(t1_2 t2_2)
NestLoop(t1_4 t2_4)
NestLoop(t1_5 t2_5)
duplication hint:
error hint:

                                 QUERY PLAN

Nested Loop
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
-> Merge Join
Merge Cond: (t2_2.id = t3_2.id)
-> Index Only Scan using t2_pkey on t2 t2_2
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Merge Join
Merge Cond: (t2_4.id = t3_4.id)
-> Index Only Scan using t2_pkey on t2 t2_4
-> Sort
Sort Key: t3_4.id
-> Seq Scan on t3 t3_4
-> Index Only Scan using t1_pkey on t1 t1_4
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
Filter: (id = $1)
-> Aggregate
Filter: (max(t1_5.id) = $1)
-> Merge Join
Merge Cond: (t2_5.id = t1_5.id)
-> Merge Join
Merge Cond: (t2_5.id = t3_5.id)
-> Index Only Scan using t2_pkey on t2 t2_5
-> Sort
Sort Key: t3_5.id
-> Seq Scan on t3 t3_5
-> Index Only Scan using t1_pkey on t1 t1_5
-> Seq Scan on t3 t3_1
Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
Index Cond: (id = $1)
(51 rows)

-- ambigous error
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Merge Join
Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)

/+NestLoop(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t2)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
NestLoop(t1 t2)

               QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Merge Join
Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)

/+Leading(t1 t2 t1)/
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near "Leading(t1 t2 t1)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading(t1 t2 t1)

               QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Merge Join
Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)

-- identifier length test
EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
QUERY PLAN

Merge Join
Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t3.id)
-> Merge Join
Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id)
-> Index Scan using t1_pkey on t1 "123456789012345678901234567890123456789012345678901234567890123"
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
(9 rows)

/+
Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
SeqScan(123456789012345678901234567890123456789012345678901234567890123)
MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
Set(123456789012345678901234567890123456789012345678901234567890123 1)
/
EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
INFO: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
LOG: pg_hint_plan:
used hint:
SeqScan(123456789012345678901234567890123456789012345678901234567890123)
MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
not used hint:
duplication hint:
error hint:
Set(123456789012345678901234567890123456789012345678901234567890123 1)

                                         QUERY PLAN

Merge Join
Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t3.id)
-> Merge Join
Merge Cond: (t2.id = "123456789012345678901234567890123456789012345678901234567890123".id)
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: "123456789012345678901234567890123456789012345678901234567890123".id
-> Seq Scan on t1 "123456789012345678901234567890123456789012345678901234567890123"
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
(11 rows)

/+
Leading(1234567890123456789012345678901234567890123456789012345678901234 t2 t3)
SeqScan(1234567890123456789012345678901234567890123456789012345678901234)
MergeJoin(1234567890123456789012345678901234567890123456789012345678901234 t2)
Set(1234567890123456789012345678901234567890123456789012345678901234 1)
Set(cursor_tuple_fraction 0.1234567890123456789012345678901234567890123456789012345678901234)
/
EXPLAIN (COSTS false) SELECT * FROM t1 "1234567890123456789012345678901234567890123456789012345678901234" JOIN t2 ON ("1234567890123456789012345678901234567890123456789012345678901234".id = t2.id) JOIN t3 ON (t2.id = t3.id);
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
INFO: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
LOG: pg_hint_plan:
used hint:
SeqScan(123456789012345678901234567890123456789012345678901234567890123)
MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
Set(cursor_tuple_fraction 0.1234567890123456789012345678901234567890123456789012345678901234)
not used hint:
duplication hint:
error hint:
Set(123456789012345678901234567890123456789012345678901234567890123 1)

                                         QUERY PLAN

SET "123456789012345678901234567890123456789012345678901234567890123" TO 1;
ERROR: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
SET "1234567890123456789012345678901234567890123456789012345678901234" TO 1;
NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
ERROR: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
SET cursor_tuple_fraction TO 1234567890123456789012345678901234567890123456789012345678901234;
ERROR: 1.23457e+63 is outside the valid range for parameter "cursor_tuple_fraction" (0 .. 1)
-- multi error
/+ Set(enable_seqscan 100)Set(seq_page_cost on)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: parameter "enable_seqscan" requires a Boolean value
INFO: invalid value for parameter "seq_page_cost": "on"
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Set(enable_seqscan 100)
Set(seq_page_cost on)

          QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
(4 rows)

-- debug log of candidate index to use IndexScan
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
QUERY PLAN

Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
Index Cond: (id = 1)
(2 rows)

/+IndexScan(t5 t5_id2)/
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
LOG: available indexes for IndexScan(t5): t5_id2
LOG: pg_hint_plan:
used hint:
IndexScan(t5 t5_id2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Index Scan using t5_id2 on t5
Index Cond: (id = 1)
(2 rows)

/+IndexScan(t5 no_exist)/
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
LOG: available indexes for IndexScan(t5):
LOG: pg_hint_plan:
used hint:
IndexScan(t5 no_exist)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+IndexScan(t5 t5_id1 t5_id2)/
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
LOG: available indexes for IndexScan(t5): t5_id2 t5_id1
LOG: pg_hint_plan:
used hint:
IndexScan(t5 t5_id1 t5_id2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Index Scan using t5_id2 on t5
Index Cond: (id = 1)
(2 rows)

/+IndexScan(t5 no_exist t5_id2)/
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
LOG: available indexes for IndexScan(t5): t5_id2
LOG: pg_hint_plan:
used hint:
IndexScan(t5 no_exist t5_id2)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Index Scan using t5_id2 on t5
Index Cond: (id = 1)
(2 rows)

/+IndexScan(t5 no_exist5 no_exist2)/
EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
LOG: available indexes for IndexScan(t5):
LOG: pg_hint_plan:
used hint:
IndexScan(t5 no_exist5 no_exist2)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

-- outer inner
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

/+Leading((t1))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
INFO: pg_hint_plan: hint syntax error at or near "Leading((t1))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading((t1))

                   QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

/+Leading((t1 t2))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

/+Leading((t1 t2 t3))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
INFO: pg_hint_plan: hint syntax error at or near "Leading((t1 t2 t3))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Leading((t1 t2 t3))

                   QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
QUERY PLAN

Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(6 rows)

/+Leading((t1 t2))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((t1 t2))
not used hint:
duplication hint:
error hint:

          QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
(6 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

/+Leading(((t1 t2) t3))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
QUERY PLAN

Nested Loop
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Nested Loop
Join Filter: (t1.val = t3.val)
-> Index Scan using t3_pkey on t3
-> Materialize
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t2_pkey on t2
Index Cond: (id = t1.id)
(14 rows)

/+Leading((((t1 t2) t3) t4))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((((t1 t2) t3) t4))
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Nested Loop
-> Nested Loop
Join Filter: (t1.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
-> Index Scan using t4_pkey on t4
Index Cond: (id = t3.id)
(12 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

/+Leading((t1 (t2 t3)))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Hash Join
Hash Cond: (t2.val = t3.val)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
(10 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
QUERY PLAN

/+Leading(((t1 t2) (t3 t4)))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) (t3 t4)))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t1.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
(14 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
QUERY PLAN

Hash Join
Hash Cond: (t2.val = t3.val)
InitPlan 1 (returns $1)
-> Limit
-> Sort
Sort Key: t1_2.id
-> Nested Loop
-> Index Scan using t2_val on t2 t2_2
Index Cond: (val > 100)
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < $1)
-> Index Scan using t2_pkey on t2
-> Hash
-> Seq Scan on t3
(18 rows)

/+Leading(((t1 t2) t3)) Leading(((t3 t1) t2))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
INFO: pg_hint_plan: hint syntax error at or near "Leading(((t1 t2) t3)) Leading(((t3 t1) t2))"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
Leading(((t3 t1) t2))
not used hint:
duplication hint:
Leading(((t1 t2) t3))
error hint:

                         QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
InitPlan 1 (returns $1)
-> Limit
-> Sort
Sort Key: t1_2.id
-> Nested Loop
-> Index Scan using t2_val on t2 t2_2
Index Cond: (val > 100)
-> Index Only Scan using t1_pkey on t1 t1_2
Index Cond: (id = t2_2.id)
-> Hash Join
Hash Cond: (t3.val = t1.val)
-> Seq Scan on t3
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < $1)
-> Hash
-> Seq Scan on t2
(19 rows)

/+Leading(((t1 t2) t3)) Leading((t1_2 t2_2))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
Leading((t1_2 t2_2))
not used hint:
duplication hint:
error hint:

                      QUERY PLAN

Hash Join
Hash Cond: (t2.val = t3.val)
InitPlan 1 (returns $0)
-> Limit
-> Merge Join
Merge Cond: (t1_2.id = t2_2.id)
-> Index Only Scan using t1_pkey on t1 t1_2
-> Sort
Sort Key: t2_2.id
-> Index Scan using t2_val on t2 t2_2
Index Cond: (val > 100)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < $0)
-> Index Scan using t2_pkey on t2
-> Hash
-> Seq Scan on t3
(18 rows)

/+Leading(((((t1 t2) t3) t1_2) t2_2))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
LOG: pg_hint_plan:
used hint:
not used hint:
Leading(((((t1 t2) t3) t1_2) t2_2))
duplication hint:
error hint:

                         QUERY PLAN

-- Specified outer/inner leading hint and join method hint at the same time
/+Leading(((t1 t2) t3))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

/+Leading(((t1 t2) t3)) MergeJoin(t1 t2)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2)
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Index Scan using t2_pkey on t2
-> Seq Scan on t3
(8 rows)

/+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2 t3)
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

                QUERY PLAN

Merge Join
Merge Cond: (t2.val = t3.val)
-> Sort
Sort Key: t2.val
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Sort
Sort Key: t3.val
-> Seq Scan on t3
(13 rows)

/+Leading(((t1 t2) t3)) MergeJoin(t1 t3)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
MergeJoin(t1 t3)
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
QUERY PLAN

/+Leading(((t1 t2) t3)) MergeJoin(t3 t4)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
MergeJoin(t3 t4)
duplication hint:
error hint:

                QUERY PLAN

Nested Loop
Join Filter: (t3.id = t4.id)
-> Nested Loop
Join Filter: (t1.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
-> Seq Scan on t4
(12 rows)

/+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3 t4)/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
MergeJoin(t1 t2 t3 t4)
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

                   QUERY PLAN

Merge Join
Merge Cond: (t3.id = t4.id)
-> Sort
Sort Key: t3.id
-> Nested Loop
Join Filter: (t1.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
(16 rows)

/+ Leading ( ( t1 ( t2 t3 ) ) ) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Hash Join
Hash Cond: (t2.val = t3.val)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
(10 rows)

/+Leading((t1(t2 t3)))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading((t1 (t2 t3)))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Hash Join
Hash Cond: (t2.val = t3.val)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
(10 rows)

/+Leading(("t1(t2" "t3)"))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
not used hint:
Leading(("t1(t2" "t3)"))
duplication hint:
error hint:

                   QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

/+ Leading ( ( ( t1 t2 ) t3 ) ) /
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

/+Leading(((t1 t2)t3))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) t3))
not used hint:
duplication hint:
error hint:

             QUERY PLAN

Nested Loop
Join Filter: (t2.val = t3.val)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
-> Seq Scan on t2
-> Seq Scan on t3
(9 rows)

/+Leading(("(t1" "t2)t3"))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
LOG: pg_hint_plan:
used hint:
not used hint:
Leading(("(t1" "t2)t3"))
duplication hint:
error hint:

                   QUERY PLAN

Hash Join
Hash Cond: (t3.val = t2.val)
-> Seq Scan on t3
-> Hash
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2
-> Hash
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
(10 rows)

/+Leading((t1(t2(t3(t4 t5)))))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading((t1 (t2 (t3 (t4 t5)))))
not used hint:
duplication hint:
error hint:

                                                        QUERY PLAN

Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Materialize
-> Merge Join
Merge Cond: (t2.id = t3.id)
-> Index Scan using t2_pkey on t2
-> Materialize
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Materialize
-> Merge Join
Merge Cond: (t4.id = t5.id)
-> Index Scan using t4_pkey on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
(16 rows)

/+Leading((t5(t4(t3(t2 t1)))))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading((t5 (t4 (t3 (t2 t1)))))
not used hint:
duplication hint:
error hint:

                            QUERY PLAN

Hash Join
Hash Cond: (t5.id = t1.id)
-> Seq Scan on t5
-> Hash
-> Merge Join
Merge Cond: (t4.id = t1.id)
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Materialize
-> Merge Join
Merge Cond: (t3.id = t1.id)
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
-> Materialize
-> Merge Join
Merge Cond: (t2.id = t1.id)
-> Index Scan using t2_pkey on t2
-> Index Scan using t1_pkey on t1
(20 rows)

/+Leading(((((t1 t2)t3)t4)t5))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading(((((t1 t2) t3) t4) t5))
not used hint:
duplication hint:
error hint:

                                      QUERY PLAN

Nested Loop
-> Merge Join
Merge Cond: (t1.id = t4.id)
-> Merge Join
Merge Cond: (t1.id = t3.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
Index Cond: (id = t1.id)
(17 rows)

/+Leading(((((t5 t4)t3)t2)t1))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading(((((t5 t4) t3) t2) t1))
not used hint:
duplication hint:
error hint:

                                               QUERY PLAN

Nested Loop
Join Filter: (t2.id = t1.id)
-> Nested Loop
Join Filter: (t3.id = t2.id)
-> Merge Join
Merge Cond: (t4.id = t3.id)
-> Merge Join
Merge Cond: (t5.id = t4.id)
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t3_pkey on t3
-> Index Scan using t2_pkey on t2
Index Cond: (id = t5.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id = t5.id)
(17 rows)

/+Leading(((t1 t2)(t3(t4 t5))))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading(((t1 t2) (t3 (t4 t5))))
not used hint:
duplication hint:
error hint:

                                                  QUERY PLAN

Merge Join
Merge Cond: (t1.id = t3.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
-> Materialize
-> Merge Join
Merge Cond: (t3.id = t4.id)
-> Index Scan using t3_pkey on t3
-> Materialize
-> Merge Join
Merge Cond: (t4.id = t5.id)
-> Index Scan using t4_pkey on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
(15 rows)

/+Leading(((t5 t4)(t3(t2 t1))))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading(((t5 t4) (t3 (t2 t1))))
not used hint:
duplication hint:
error hint:

                                         QUERY PLAN

Merge Join
Merge Cond: (t4.id = t1.id)
-> Merge Join
Merge Cond: (t5.id = t4.id)
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Materialize
-> Merge Join
Merge Cond: (t3.id = t1.id)
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
-> Materialize
-> Merge Join
Merge Cond: (t2.id = t1.id)
-> Index Scan using t2_pkey on t2
-> Index Scan using t1_pkey on t1
(19 rows)

/+Leading((((t1 t2)t3)(t4 t5)))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading((((t1 t2) t3) (t4 t5)))
not used hint:
duplication hint:
error hint:

                                            QUERY PLAN

Merge Join
Merge Cond: (t1.id = t4.id)
-> Merge Join
Merge Cond: (t1.id = t3.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t3.id
-> Seq Scan on t3
-> Materialize
-> Merge Join
Merge Cond: (t4.id = t5.id)
-> Index Scan using t4_pkey on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
(16 rows)

/+Leading((((t5 t4)t3)(t2 t1)))/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
LOG: pg_hint_plan:
used hint:
Leading((((t5 t4) t3) (t2 t1)))
not used hint:
duplication hint:
error hint:

                                            QUERY PLAN

Merge Join
Merge Cond: (t3.id = t1.id)
-> Merge Join
Merge Cond: (t4.id = t3.id)
-> Merge Join
Merge Cond: (t5.id = t4.id)
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t3_pkey on t3
-> Materialize
-> Merge Join
Merge Cond: (t2.id = t1.id)
-> Index Scan using t2_pkey on t2
-> Index Scan using t1_pkey on t1
(16 rows)

-- inherite table test to specify the index's name
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
QUERY PLAN

Append
-> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1 p2_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(9 rows)

/+IndexScan(p2 p2_pkey)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Append
-> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_pkey on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)

/+IndexScan(p2 p2_id_val_idx)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_id_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_id_val_idx)
not used hint:
duplication hint:
error hint:

                     QUERY PLAN

Append
-> Index Scan using p2_id_val_idx on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)

/+IndexScan(p2 p2_val_id_idx)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val_id_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_val_id_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_id_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_id_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_id_idx)
not used hint:
duplication hint:
error hint:

                     QUERY PLAN

Append
-> Index Scan using p2_val_id_idx on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_val_id_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c1_val_id_idx on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c2_val_id_idx on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)

EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
QUERY PLAN

Append
-> Seq Scan on p2 p2_1
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1 p2_2
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c2 p2_3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3 p2_4
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c4 p2_5
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c1 p2_6
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c2 p2_7
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3_c1 p2_8
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3_c2 p2_9
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
(19 rows)

-- Inhibit parallel exection to avoid interfaring the hint
set max_parallel_workers_per_gather to 0;
/+ IndexScan(p2 p2_val)/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1):
LOG: available indexes for IndexScan(p2_c2):
LOG: available indexes for IndexScan(p2_c3):
LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: available indexes for IndexScan(p2_c3_c1):
LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val)
not used hint:
duplication hint:
error hint:

                                   QUERY PLAN

                  QUERY PLAN

/+IndexScan(p2 p2_id2_val)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_id2_val
LOG: available indexes for IndexScan(p2_c1): p2_c1_id2_val
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_id2_val)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Append
-> Index Scan using p2_id2_val on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_id2_val on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(11 rows)

/+IndexScan(p2 p2_val2_id)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val2_id
LOG: available indexes for IndexScan(p2_c1):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val2_id)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Append
-> Index Scan using p2_val2_id on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on p2_c1 p2_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(10 rows)

                  QUERY PLAN

/+IndexScan(p2 p2_c1_id_val_idx)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_c1_id_val_idx)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

Append
-> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(10 rows)

/+IndexScan(p2 no_exist)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 no_exist)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

/+IndexScan(p2 p2_pkey p2_c1_id_val_idx)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey p2_c1_id_val_idx)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Append
-> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)

/+IndexScan(p2 p2_pkey no_exist)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey no_exist)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

/+IndexScan(p2 p2_c1_id_val_idx no_exist)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_c1_id_val_idx no_exist)
not used hint:
duplication hint:
error hint:

                          QUERY PLAN

/+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

/+IndexScan(p2 p2_val_idx)/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx)
not used hint:
duplication hint:
error hint:

                        QUERY PLAN

Append
-> Index Scan using p2_val_idx on p2 p2_1
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_val_idx on p2_c1 p2_2
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c2_val_idx on p2_c2 p2_3
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c3_val_idx on p2_c3 p2_4
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c4_val_idx on p2_c4 p2_5
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2_6
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2_7
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2_8
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2_9
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
(28 rows)

/+IndexScan(p2 p2_expr)/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_expr
LOG: available indexes for IndexScan(p2_c1): p2_c1_expr_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_expr_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_expr_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_expr_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_expr_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_expr_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_expr_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_expr_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_expr)
not used hint:
duplication hint:
error hint:

                                   QUERY PLAN

/+IndexScan(p2 p2_val_idx6)/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val_idx6
LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx7
LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx7
LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx7
LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx7
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx6)
not used hint:
duplication hint:
error hint:

                                   QUERY PLAN

/+IndexScan(p2 p2_val_idx p2_val_idx6)/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val_idx6 p2_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx7 p2_c1_val_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx7 p2_c2_val_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx7 p2_c3_val_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx7 p2_c4_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7 p2_c1_c1_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7 p2_c1_c2_val_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7 p2_c3_c1_val_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7 p2_c3_c2_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx p2_val_idx6)
not used hint:
duplication hint:
error hint:

                        QUERY PLAN

-- regular expression
-- ordinary table
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
QUERY PLAN

Index Only Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
Index Cond: (id = 1)
(2 rows)

/+ IndexScanRegexp(t5 t5_[^i].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexScanRegexp(t5): t5_val t5_pkey
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(t5 t5_[^i].)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

Index Scan using t5_pkey on t5
Index Cond: (id = 1)
(2 rows)

/+ IndexScanRegexp(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexScanRegexp(t5): t5_id3 t5_id2 t5_id1
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

      QUERY PLAN

Index Scan using t5_id3 on t5
Index Cond: (id = 1)
(2 rows)

/+ IndexScanRegexp(t5 t5[^_].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(t5 t5[^_].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ IndexScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ IndexScan(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexScan(t5):
LOG: pg_hint_plan:
used hint:
IndexScan(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ IndexOnlyScanRegexp(t5 t5_[^i].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexOnlyScanRegexp(t5): t5_val t5_pkey
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(t5 t5_[^i].)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Index Only Scan using t5_pkey on t5
Index Cond: (id = 1)
(2 rows)

/+ IndexOnlyScanRegexp(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexOnlyScanRegexp(t5): t5_id3 t5_id2 t5_id1
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Index Only Scan using t5_id3 on t5
Index Cond: (id = 1)
(2 rows)

/+ IndexOnlyScanRegexp(t5 t5[^_].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexOnlyScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(t5 t5[^_].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ IndexOnlyScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexOnlyScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ IndexOnlyScan(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for IndexOnlyScan(t5):
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ BitmapScanRegexp(t5 t5_[^i].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for BitmapScanRegexp(t5): t5_val t5_pkey
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(t5 t5_[^i].)
not used hint:
duplication hint:
error hint:

         QUERY PLAN

Bitmap Heap Scan on t5
Recheck Cond: (id = 1)
-> Bitmap Index Scan on t5_pkey
Index Cond: (id = 1)
(4 rows)

/+ BitmapScanRegexp(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for BitmapScanRegexp(t5): t5_id3 t5_id2 t5_id1
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

        QUERY PLAN

Bitmap Heap Scan on t5
Recheck Cond: (id = 1)
-> Bitmap Index Scan on t5_id3
Index Cond: (id = 1)
(4 rows)

/+ BitmapScanRegexp(t5 t5[^_].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for BitmapScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(t5 t5[^_].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ BitmapScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for BitmapScanRegexp(t5):
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(t5 ^.t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

/+ BitmapScan(t5 t5_id[0-9].)/
EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
LOG: available indexes for BitmapScan(t5):
LOG: pg_hint_plan:
used hint:
BitmapScan(t5 t5_id[0-9].)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t5
Filter: (id = 1)
(2 rows)

-- Inheritance
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
QUERY PLAN

Append
-> Seq Scan on p1 p1_1
Filter: (val = 1)
-> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
-> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
-> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
-> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
-> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
-> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
-> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
-> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)

/+ IndexScanRegexp(p1 p1_.[^0-9]$)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexScanRegexp(p1): p1_pkey
LOG: available indexes for IndexScanRegexp(p1_c1): p1_c1_pkey
LOG: available indexes for IndexScanRegexp(p1_c2): p1_c2_pkey
LOG: available indexes for IndexScanRegexp(p1_c3): p1_c3_pkey
LOG: available indexes for IndexScanRegexp(p1_c4): p1_c4_pkey
LOG: available indexes for IndexScanRegexp(p1_c1_c1): p1_c1_c1_pkey
LOG: available indexes for IndexScanRegexp(p1_c1_c2): p1_c1_c2_pkey
LOG: available indexes for IndexScanRegexp(p1_c3_c1): p1_c3_c1_pkey
LOG: available indexes for IndexScanRegexp(p1_c3_c2): p1_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(p1 p1_.[^0-9]$)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ IndexScanRegexp(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexScanRegexp(p1): p1_val2
LOG: available indexes for IndexScanRegexp(p1_c1): p1_c1_val2
LOG: available indexes for IndexScanRegexp(p1_c2): p1_c2_val2
LOG: available indexes for IndexScanRegexp(p1_c3): p1_c3_val2
LOG: available indexes for IndexScanRegexp(p1_c4): p1_c4_val2
LOG: available indexes for IndexScanRegexp(p1_c1_c1): p1_c1_c1_val2
LOG: available indexes for IndexScanRegexp(p1_c1_c2): p1_c1_c2_val2
LOG: available indexes for IndexScanRegexp(p1_c3_c1): p1_c3_c1_val2
LOG: available indexes for IndexScanRegexp(p1_c3_c2): p1_c3_c2_val2
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN

Append
-> Index Scan using p1_val2 on p1 p1_1
Index Cond: (val = 1)
-> Index Scan using p1_c1_val2 on p1_c1 p1_2
Index Cond: (val = 1)
-> Index Scan using p1_c2_val2 on p1_c2 p1_3
Index Cond: (val = 1)
-> Index Scan using p1_c3_val2 on p1_c3 p1_4
Index Cond: (val = 1)
-> Index Scan using p1_c4_val2 on p1_c4 p1_5
Index Cond: (val = 1)
-> Index Scan using p1_c1_c1_val2 on p1_c1_c1 p1_6
Index Cond: (val = 1)
-> Index Scan using p1_c1_c2_val2 on p1_c1_c2 p1_7
Index Cond: (val = 1)
-> Index Scan using p1_c3_c1_val2 on p1_c3_c1 p1_8
Index Cond: (val = 1)
-> Index Scan using p1_c3_c2_val2 on p1_c3_c2 p1_9
Index Cond: (val = 1)
(19 rows)

/+ IndexScanRegexp(p1 p1[^_].)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexScanRegexp(p1):
LOG: available indexes for IndexScanRegexp(p1_c1):
LOG: available indexes for IndexScanRegexp(p1_c2):
LOG: available indexes for IndexScanRegexp(p1_c3):
LOG: available indexes for IndexScanRegexp(p1_c4):
LOG: available indexes for IndexScanRegexp(p1_c1_c1):
LOG: available indexes for IndexScanRegexp(p1_c1_c2):
LOG: available indexes for IndexScanRegexp(p1_c3_c1):
LOG: available indexes for IndexScanRegexp(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(p1 p1[^_].)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ IndexScan(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexScan(p1):
LOG: available indexes for IndexScan(p1_c1):
LOG: available indexes for IndexScan(p1_c2):
LOG: available indexes for IndexScan(p1_c3):
LOG: available indexes for IndexScan(p1_c4):
LOG: available indexes for IndexScan(p1_c1_c1):
LOG: available indexes for IndexScan(p1_c1_c2):
LOG: available indexes for IndexScan(p1_c3_c1):
LOG: available indexes for IndexScan(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ IndexOnlyScanRegexp(p1 p1_.[^0-9]$)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexOnlyScanRegexp(p1): p1_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c1): p1_c1_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c2): p1_c2_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c3): p1_c3_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c4): p1_c4_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1): p1_c1_c1_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2): p1_c1_c2_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1): p1_c3_c1_pkey
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2): p1_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(p1 p1_.[^0-9]$)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ IndexOnlyScanRegexp(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexOnlyScanRegexp(p1): p1_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c1): p1_c1_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c2): p1_c2_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c3): p1_c3_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c4): p1_c4_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1): p1_c1_c1_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2): p1_c1_c2_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1): p1_c3_c1_val2
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2): p1_c3_c2_val2
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

                     QUERY PLAN

Append
-> Index Only Scan using p1_val2 on p1 p1_1
Index Cond: (val = 1)
-> Index Only Scan using p1_c1_val2 on p1_c1 p1_2
Index Cond: (val = 1)
-> Index Only Scan using p1_c2_val2 on p1_c2 p1_3
Index Cond: (val = 1)
-> Index Only Scan using p1_c3_val2 on p1_c3 p1_4
Index Cond: (val = 1)
-> Index Only Scan using p1_c4_val2 on p1_c4 p1_5
Index Cond: (val = 1)
-> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1 p1_6
Index Cond: (val = 1)
-> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2 p1_7
Index Cond: (val = 1)
-> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1 p1_8
Index Cond: (val = 1)
-> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2 p1_9
Index Cond: (val = 1)
(19 rows)

/+ IndexOnlyScanRegexp(p1 p1[^_].)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexOnlyScanRegexp(p1):
LOG: available indexes for IndexOnlyScanRegexp(p1_c1):
LOG: available indexes for IndexOnlyScanRegexp(p1_c2):
LOG: available indexes for IndexOnlyScanRegexp(p1_c3):
LOG: available indexes for IndexOnlyScanRegexp(p1_c4):
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1):
LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2):
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1):
LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(p1 p1[^_].)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ IndexOnlyScan(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for IndexOnlyScan(p1):
LOG: available indexes for IndexOnlyScan(p1_c1):
LOG: available indexes for IndexOnlyScan(p1_c2):
LOG: available indexes for IndexOnlyScan(p1_c3):
LOG: available indexes for IndexOnlyScan(p1_c4):
LOG: available indexes for IndexOnlyScan(p1_c1_c1):
LOG: available indexes for IndexOnlyScan(p1_c1_c2):
LOG: available indexes for IndexOnlyScan(p1_c3_c1):
LOG: available indexes for IndexOnlyScan(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ BitmapScanRegexp(p1 p1_.[^0-9]$)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for BitmapScanRegexp(p1): p1_pkey
LOG: available indexes for BitmapScanRegexp(p1_c1): p1_c1_pkey
LOG: available indexes for BitmapScanRegexp(p1_c2): p1_c2_pkey
LOG: available indexes for BitmapScanRegexp(p1_c3): p1_c3_pkey
LOG: available indexes for BitmapScanRegexp(p1_c4): p1_c4_pkey
LOG: available indexes for BitmapScanRegexp(p1_c1_c1): p1_c1_c1_pkey
LOG: available indexes for BitmapScanRegexp(p1_c1_c2): p1_c1_c2_pkey
LOG: available indexes for BitmapScanRegexp(p1_c3_c1): p1_c3_c1_pkey
LOG: available indexes for BitmapScanRegexp(p1_c3_c2): p1_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(p1 p1_.[^0-9]$)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ BitmapScanRegexp(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for BitmapScanRegexp(p1): p1_val2
LOG: available indexes for BitmapScanRegexp(p1_c1): p1_c1_val2
LOG: available indexes for BitmapScanRegexp(p1_c2): p1_c2_val2
LOG: available indexes for BitmapScanRegexp(p1_c3): p1_c3_val2
LOG: available indexes for BitmapScanRegexp(p1_c4): p1_c4_val2
LOG: available indexes for BitmapScanRegexp(p1_c1_c1): p1_c1_c1_val2
LOG: available indexes for BitmapScanRegexp(p1_c1_c2): p1_c1_c2_val2
LOG: available indexes for BitmapScanRegexp(p1_c3_c1): p1_c3_c1_val2
LOG: available indexes for BitmapScanRegexp(p1_c3_c2): p1_c3_c2_val2
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

               QUERY PLAN

Append
-> Bitmap Heap Scan on p1 p1_1
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c1 p1_2
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c2 p1_3
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c2_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c3 p1_4
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c4 p1_5
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c4_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c1_c1 p1_6
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_c1_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c1_c2 p1_7
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_c2_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c3_c1 p1_8
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_c1_val2
Index Cond: (val = 1)
-> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_c2_val2
Index Cond: (val = 1)
(37 rows)

/+ BitmapScanRegexp(p1 p1[^_].)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for BitmapScanRegexp(p1):
LOG: available indexes for BitmapScanRegexp(p1_c1):
LOG: available indexes for BitmapScanRegexp(p1_c2):
LOG: available indexes for BitmapScanRegexp(p1_c3):
LOG: available indexes for BitmapScanRegexp(p1_c4):
LOG: available indexes for BitmapScanRegexp(p1_c1_c1):
LOG: available indexes for BitmapScanRegexp(p1_c1_c2):
LOG: available indexes for BitmapScanRegexp(p1_c3_c1):
LOG: available indexes for BitmapScanRegexp(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(p1 p1[^_].)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

/+ BitmapScan(p1 p1_.val2.)/
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
LOG: available indexes for BitmapScan(p1):
LOG: available indexes for BitmapScan(p1_c1):
LOG: available indexes for BitmapScan(p1_c2):
LOG: available indexes for BitmapScan(p1_c3):
LOG: available indexes for BitmapScan(p1_c4):
LOG: available indexes for BitmapScan(p1_c1_c1):
LOG: available indexes for BitmapScan(p1_c1_c2):
LOG: available indexes for BitmapScan(p1_c3_c1):
LOG: available indexes for BitmapScan(p1_c3_c2):
LOG: pg_hint_plan:
used hint:
BitmapScan(p1 p1_.val2.)
not used hint:
duplication hint:
error hint:

       QUERY PLAN

-- search from hint table
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;', '', 'IndexScan(t1)');
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN SELECT * FROM t1 WHERE t1.id = ?;', '', 'BitmapScan(t1)');
SELECT * FROM hint_plan.hints ORDER BY id;
id | norm_query_string | application_name | hints
----+----------------------------------------------------------+------------------+----------------
1 | EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?; | | SeqScan(t1)
2 | EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?; | | IndexScan(t1)
3 | EXPLAIN SELECT * FROM t1 WHERE t1.id = ?; | | BitmapScan(t1)
(3 rows)

SET pg_hint_plan.enable_hint_table = on;
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

 QUERY PLAN

Seq Scan on t1
Filter: (id = 1)
(2 rows)

SET pg_hint_plan.enable_hint_table = off;
EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
QUERY PLAN

Index Scan using t1_pkey on t1
Index Cond: (id = 1)
(2 rows)

TRUNCATE hint_plan.hints;
VACUUM ANALYZE hint_plan.hints;
-- plpgsql test
EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
QUERY PLAN

Index Only Scan using t1_pkey on t1
Index Cond: (id = 1)
(2 rows)

-- static function
CREATE FUNCTION testfunc() RETURNS RECORD AS $$
DECLARE
ret record;
BEGIN
SELECT /+ SeqScan(t1) / * INTO ret FROM t1 LIMIT 1;
RETURN ret;
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT /+ SeqScan(t1) / * FROM t1 LIMIT 1"
PL/pgSQL function testfunc() line 5 at SQL statement
testfunc

(1,1)
(1 row)

-- dynamic function
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS void AS $$
BEGIN
EXECUTE format('/+ SeqScan(t1) / SELECT * FROM t1');
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "/+ SeqScan(t1) / SELECT * FROM t1"
PL/pgSQL function testfunc() line 3 at EXECUTE
testfunc

(1 row)

-- This should not use SeqScan(t1)
/+ IndexScan(t1) / SELECT * from t1 LIMIT 1;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:

id | val
----+-----
1 | 1
(1 row)

-- Perform
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS void AS $$
BEGIN
PERFORM 1, /+ SeqScan(t1) / * from t1;
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT 1, /+ SeqScan(t1) / * from t1"
PL/pgSQL function testfunc() line 3 at PERFORM
testfunc

(1 row)

-- FOR loop
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS int AS $$
DECLARE
sum int;
v int;
BEGIN
sum := 0;
FOR v IN SELECT /+ SeqScan(t1) / v FROM t1 ORDER BY id LOOP
sum := sum + v;
END LOOP;
RETURN v;
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT /+ SeqScan(t1) / v FROM t1 ORDER BY id"
PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
testfunc

(1 row)

-- Dynamic FOR loop
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS int AS $$
DECLARE
sum int;
v int;
i int;
BEGIN
sum := 0;
FOR v IN EXECUTE 'SELECT /+ SeqScan(t1) / val FROM t1 ORDER BY id' LOOP
sum := sum + v;
END LOOP;
RETURN v;
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT /+ SeqScan(t1) / val FROM t1 ORDER BY id"
PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
testfunc

(1 row)

-- Cursor FOR loop
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS int AS $$
DECLARE
ref CURSOR FOR SELECT /+ SeqScan(t1) / * FROM t1 ORDER BY id;
rec record;
sum int := 0;
BEGIN
FOR rec IN ref LOOP
sum := sum + rec.val;
END LOOP;
RETURN sum;
END;
$$ LANGUAGE plpgsql;
SELECT testfunc();
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT /+ SeqScan(t1) / * FROM t1 ORDER BY id"
PL/pgSQL function testfunc() line 7 at FOR over cursor
testfunc

495000
(1 row)

-- RETURN QUERY
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
BEGIN
RETURN QUERY SELECT /+ SeqScan(t1) / * FROM t1 ORDER BY id;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM testfunc() LIMIT 1;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "SELECT /+ SeqScan(t1) / * FROM t1 ORDER BY id"
PL/pgSQL function testfunc() line 3 at RETURN QUERY
id | val
----+-----
1 | 1
(1 row)

-- Test for error exit from inner SQL statement.
DROP FUNCTION testfunc();
CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
BEGIN
RETURN QUERY SELECT /+ SeqScan(t1) / * FROM ttx ORDER BY id;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM testfunc() LIMIT 1;
ERROR: relation "ttx" does not exist
LINE 1: SELECT /+ SeqScan(t1) / * FROM ttx ORDER BY id
^
QUERY: SELECT /+ SeqScan(t1) / * FROM ttx ORDER BY id
CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
-- this should not use SeqScan(t1) hint.
/+ IndexScan(t1) / SELECT * from t1 LIMIT 1;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:

id | val
----+-----
1 | 1
(1 row)

DROP FUNCTION testfunc();
DROP EXTENSION pg_hint_plan;
CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
DECLARE
rows int;
BEGIN
rows = 1;
while rows > 0 LOOP
PERFORM pg_stat_reset();
PERFORM pg_sleep(0.5);
SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Dynamic query in pl/pgsql
CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
DECLARE c int;
BEGIN
EXECUTE '/+ IndexScan(t1) / SELECT count(*) FROM t1 WHERE id < $1'
INTO c USING x;
RETURN c;
END;
$$ VOLATILE LANGUAGE plpgsql;
vacuum analyze t1;
SET pg_hint_plan.enable_hint = false;
SELECT pg_sleep(1);
pg_sleep

(1 row)

SELECT reset_stats_and_wait();
reset_stats_and_wait

(1 row)

SELECT dynsql1(9000);
dynsql1

(1 row)

SELECT pg_sleep(1);
pg_sleep

(1 row)

SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
relname | seq_scan | idx_scan
---------+----------+----------
t1 | t | f
(1 row)

SET pg_hint_plan.enable_hint = true;
SELECT reset_stats_and_wait();
reset_stats_and_wait

(1 row)

SELECT dynsql1(9000);
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "/+ IndexScan(t1) / SELECT count(*) FROM t1 WHERE id < $1"
PL/pgSQL function dynsql1(integer) line 4 at EXECUTE
dynsql1

(1 row)

SELECT pg_sleep(1);
pg_sleep

(1 row)

-- Looped dynamic query in pl/pgsql
CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
DECLARE
c text;
s int;
BEGIN
r := 0;
FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
FOR s IN EXECUTE '/+ IndexScan(' || c || ' ' || c || '_pkey) / SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
r := r + s;
END LOOP;
END LOOP;
END;
$$ VOLATILE LANGUAGE plpgsql;
SET pg_hint_plan.enable_hint = false;
SELECT reset_stats_and_wait();
reset_stats_and_wait

(1 row)

SELECT dynsql2(9000);
dynsql2

(1 row)

SELECT pg_sleep(1);
pg_sleep

(1 row)

-- one of the index scans happened while planning.
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
relname | seq_scan | idx_scan
---------+----------+----------
p1_c1 | 1 | 0
p1_c2 | 1 | 1
(2 rows)

SET pg_hint_plan.enable_hint = true;
SELECT reset_stats_and_wait();
reset_stats_and_wait

(1 row)

SELECT dynsql2(9000);
LOG: available indexes for IndexScan(p1_c1): p1_c1_pkey
CONTEXT: SQL statement "/+ IndexScan(p1_c1 p1_c1_pkey) / SELECT sum(val) FROM p1_c1 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey
CONTEXT: SQL statement "/+ IndexScan(p1_c1 p1_c1_pkey) / SELECT sum(val) FROM p1_c1 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey
CONTEXT: SQL statement "/+ IndexScan(p1_c1 p1_c1_pkey) / SELECT sum(val) FROM p1_c1 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
LOG: pg_hint_plan:
used hint:
IndexScan(p1_c1 p1_c1_pkey)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "/+ IndexScan(p1_c1 p1_c1_pkey) / SELECT sum(val) FROM p1_c1 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
LOG: available indexes for IndexScan(p1_c2): p1_c2_pkey
CONTEXT: SQL statement "/+ IndexScan(p1_c2 p1_c2_pkey) / SELECT sum(val) FROM p1_c2 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
LOG: pg_hint_plan:
used hint:
IndexScan(p1_c2 p1_c2_pkey)
not used hint:
duplication hint:
error hint:

CONTEXT: SQL statement "/+ IndexScan(p1_c2 p1_c2_pkey) / SELECT sum(val) FROM p1_c2 WHERE id < 9000"
PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
dynsql2

(1 row)

SELECT pg_sleep(1);
pg_sleep

(1 row)

-- the index scan happened while planning.
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
relname | seq_scan | idx_scan
---------+----------+----------
p1_c1 | 0 | 1
p1_c2 | 0 | 2
(2 rows)

-- Subqueries on inheritance tables under UNION
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION ALL
SELECT val::int FROM p2 WHERE id < 1000;
QUERY PLAN

Append
-> Append
-> Seq Scan on p1 p1_1
Filter: (val < 1000)
-> Seq Scan on p1_c1 p1_2
Filter: (val < 1000)
-> Seq Scan on p1_c2 p1_3
Filter: (val < 1000)
-> Seq Scan on p1_c3 p1_4
Filter: (val < 1000)
-> Seq Scan on p1_c4 p1_5
Filter: (val < 1000)
-> Seq Scan on p1_c1_c1 p1_6
Filter: (val < 1000)
-> Seq Scan on p1_c1_c2 p1_7
Filter: (val < 1000)
-> Seq Scan on p1_c3_c1 p1_8
Filter: (val < 1000)
-> Seq Scan on p1_c3_c2 p1_9
Filter: (val < 1000)
-> Result
-> Append
-> Seq Scan on p2 p2_1
Filter: (id < 1000)
-> Seq Scan on p2_c1 p2_2
Filter: (id < 1000)
-> Seq Scan on p2_c2 p2_3
Filter: (id < 1000)
-> Seq Scan on p2_c3 p2_4
Filter: (id < 1000)
-> Seq Scan on p2_c4 p2_5
Filter: (id < 1000)
-> Seq Scan on p2_c1_c1 p2_6
Filter: (id < 1000)
-> Seq Scan on p2_c1_c2 p2_7
Filter: (id < 1000)
-> Seq Scan on p2_c3_c1 p2_8
Filter: (id < 1000)
-> Seq Scan on p2_c3_c2 p2_9
Filter: (id < 1000)
(40 rows)

/+ IndexScan(p1 p1_val2) /
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION ALL
SELECT val::int FROM p2 WHERE id < 1000;
LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
LOG: pg_hint_plan:
used hint:
IndexScan(p1 p1_val2)
not used hint:
duplication hint:
error hint:

                     QUERY PLAN

Append
-> Append
-> Index Scan using p1_val3 on p1 p1_1
Index Cond: (val < 1000)
-> Index Scan using p1_c1_val3 on p1_c1 p1_2
Index Cond: (val < 1000)
-> Index Scan using p1_c2_val3 on p1_c2 p1_3
Index Cond: (val < 1000)
-> Index Scan using p1_c3_val3 on p1_c3 p1_4
Index Cond: (val < 1000)
-> Index Scan using p1_c4_val3 on p1_c4 p1_5
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
Index Cond: (val < 1000)
-> Result
-> Append
-> Seq Scan on p2 p2_1
Filter: (id < 1000)
-> Seq Scan on p2_c1 p2_2
Filter: (id < 1000)
-> Seq Scan on p2_c2 p2_3
Filter: (id < 1000)
-> Seq Scan on p2_c3 p2_4
Filter: (id < 1000)
-> Seq Scan on p2_c4 p2_5
Filter: (id < 1000)
-> Seq Scan on p2_c1_c1 p2_6
Filter: (id < 1000)
-> Seq Scan on p2_c1_c2 p2_7
Filter: (id < 1000)
-> Seq Scan on p2_c3_c1 p2_8
Filter: (id < 1000)
-> Seq Scan on p2_c3_c2 p2_9
Filter: (id < 1000)
(40 rows)

/+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) /
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION ALL
SELECT val::int FROM p2 WHERE id < 1000;
LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
LOG: available indexes for IndexScan(p2): p2_id_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p1 p1_val2)
IndexScan(p2 p2_id_val_idx)
not used hint:
duplication hint:
error hint:

                           QUERY PLAN

Append
-> Append
-> Index Scan using p1_val3 on p1 p1_1
Index Cond: (val < 1000)
-> Index Scan using p1_c1_val3 on p1_c1 p1_2
Index Cond: (val < 1000)
-> Index Scan using p1_c2_val3 on p1_c2 p1_3
Index Cond: (val < 1000)
-> Index Scan using p1_c3_val3 on p1_c3 p1_4
Index Cond: (val < 1000)
-> Index Scan using p1_c4_val3 on p1_c4 p1_5
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
Index Cond: (val < 1000)
-> Result
-> Append
-> Index Scan using p2_id_val_idx on p2 p2_1
Index Cond: (id < 1000)
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: (id < 1000)
-> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
Index Cond: (id < 1000)
-> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
Index Cond: (id < 1000)
-> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
Index Cond: (id < 1000)
(40 rows)

-- union all case
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION
SELECT val::int FROM p2 WHERE id < 1000;
QUERY PLAN

HashAggregate
Group Key: p1.val
-> Append
-> Append
-> Seq Scan on p1 p1_1
Filter: (val < 1000)
-> Seq Scan on p1_c1 p1_2
Filter: (val < 1000)
-> Seq Scan on p1_c2 p1_3
Filter: (val < 1000)
-> Seq Scan on p1_c3 p1_4
Filter: (val < 1000)
-> Seq Scan on p1_c4 p1_5
Filter: (val < 1000)
-> Seq Scan on p1_c1_c1 p1_6
Filter: (val < 1000)
-> Seq Scan on p1_c1_c2 p1_7
Filter: (val < 1000)
-> Seq Scan on p1_c3_c1 p1_8
Filter: (val < 1000)
-> Seq Scan on p1_c3_c2 p1_9
Filter: (val < 1000)
-> Result
-> Append
-> Seq Scan on p2 p2_1
Filter: (id < 1000)
-> Seq Scan on p2_c1 p2_2
Filter: (id < 1000)
-> Seq Scan on p2_c2 p2_3
Filter: (id < 1000)
-> Seq Scan on p2_c3 p2_4
Filter: (id < 1000)
-> Seq Scan on p2_c4 p2_5
Filter: (id < 1000)
-> Seq Scan on p2_c1_c1 p2_6
Filter: (id < 1000)
-> Seq Scan on p2_c1_c2 p2_7
Filter: (id < 1000)
-> Seq Scan on p2_c3_c1 p2_8
Filter: (id < 1000)
-> Seq Scan on p2_c3_c2 p2_9
Filter: (id < 1000)
(42 rows)

/+ IndexScan(p2 p2_id_val_idx) /
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION
SELECT val::int FROM p2 WHERE id < 1000;
LOG: available indexes for IndexScan(p2): p2_id_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_id_val_idx)
not used hint:
duplication hint:
error hint:

                              QUERY PLAN

HashAggregate
Group Key: p1.val
-> Append
-> Append
-> Seq Scan on p1 p1_1
Filter: (val < 1000)
-> Seq Scan on p1_c1 p1_2
Filter: (val < 1000)
-> Seq Scan on p1_c2 p1_3
Filter: (val < 1000)
-> Seq Scan on p1_c3 p1_4
Filter: (val < 1000)
-> Seq Scan on p1_c4 p1_5
Filter: (val < 1000)
-> Seq Scan on p1_c1_c1 p1_6
Filter: (val < 1000)
-> Seq Scan on p1_c1_c2 p1_7
Filter: (val < 1000)
-> Seq Scan on p1_c3_c1 p1_8
Filter: (val < 1000)
-> Seq Scan on p1_c3_c2 p1_9
Filter: (val < 1000)
-> Result
-> Append
-> Index Scan using p2_id_val_idx on p2 p2_1
Index Cond: (id < 1000)
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: (id < 1000)
-> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
Index Cond: (id < 1000)
-> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
Index Cond: (id < 1000)
-> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
Index Cond: (id < 1000)
(42 rows)

/+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) /
EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
UNION
SELECT val::int FROM p2 WHERE id < 1000;
LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
LOG: available indexes for IndexScan(p2): p2_id_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p1 p1_val2)
IndexScan(p2 p2_id_val_idx)
not used hint:
duplication hint:
error hint:

                              QUERY PLAN

HashAggregate
Group Key: p1.val
-> Append
-> Append
-> Index Scan using p1_val3 on p1 p1_1
Index Cond: (val < 1000)
-> Index Scan using p1_c1_val3 on p1_c1 p1_2
Index Cond: (val < 1000)
-> Index Scan using p1_c2_val3 on p1_c2 p1_3
Index Cond: (val < 1000)
-> Index Scan using p1_c3_val3 on p1_c3 p1_4
Index Cond: (val < 1000)
-> Index Scan using p1_c4_val3 on p1_c4 p1_5
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
Index Cond: (val < 1000)
-> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
Index Cond: (val < 1000)
-> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
Index Cond: (val < 1000)
-> Result
-> Append
-> Index Scan using p2_id_val_idx on p2 p2_1
Index Cond: (id < 1000)
-> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: (id < 1000)
-> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
Index Cond: (id < 1000)
-> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
Index Cond: (id < 1000)
-> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
Index Cond: (id < 1000)
-> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
Index Cond: (id < 1000)
-> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
Index Cond: (id < 1000)
(42 rows)

--
-- Rows hint tests
--
-- Explain result includes "Planning time" if COSTS is enabled, but
-- this test needs it enabled for get rows count. So do tests via psql
-- and grep -v the mutable line.
-- Parse error check
/+ Rows() / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near " "
DETAIL: Rows hint needs at least one relation followed by one correction term.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Rows()

?column?

(1 row)

/+ Rows(x) / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near " "
DETAIL: Rows hint needs at least one relation followed by one correction term.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Rows()

?column?

(1 row)

-- value types
\o results/pg_hint_plan.tmpout
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=1000 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 #99) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 #99)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=99 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 +99) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 +99)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=1099 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 -99) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 -99)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=901 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 99) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 99)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=99000 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 0.01) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 0.01)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=10 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 #aa) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
INFO: pg_hint_plan: hint syntax error at or near "aa"
DETAIL: Rows hint requires valid number as rows estimation.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Rows(t1 t2 #aa)

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 /99) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
INFO: pg_hint_plan: hint syntax error at or near "/99"
DETAIL: Unrecognized rows value type notation.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
Rows(t1 t2 /99)

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

-- round up to 1
\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 -99999) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 -99999)
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 -99999)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)

-- complex join tree
\o results/pg_hint_plan.tmpout
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=10 width=xxx)
Merge Cond: (t1.id = t3.id)
-> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
-> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t2 #22) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 #22)
not used hint:
duplication hint:
error hint:

\o
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (t1.id = t3.id)
-> Merge Join (cost=xxx..xxx rows=22 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
-> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)

\o results/pg_hint_plan.tmpout
/+ Rows(t1 t3 10) /
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
LOG: pg_hint_plan:
used hint:
Rows(t1 t3 10)
not used hint:
duplication hint:
error hint:

\o
set max_parallel_workers_per_gather to DEFAULT;
! sql/maskout.sh results/pg_hint_plan.tmpout
QUERY PLAN

Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (t1.id = t3.id)
-> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
-> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
-> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)

! rm results/pg_hint_plan.tmpout
-- hint error level
set client_min_messages to 'DEBUG1';
set pg_hint_plan.debug_level to 'verbose';
/+ SeqScan( / SELECT 1;
INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: Closing parenthesis is necessary.
?column?

(1 row)

/+ SeqScan(t1) / SELECT * FROM t1 LIMIT 0;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

id | val
----+-----
(0 rows)

set pg_hint_plan.message_level to 'DEBUG1';
set pg_hint_plan.parse_messages to 'NOTICE';
/+ SeqScan( / SELECT 1;
NOTICE: pg_hint_plan: hint syntax error at or near ""
DETAIL: Closing parenthesis is necessary.
?column?

(1 row)

/+ SeqScan(t1) / SELECT * FROM t1 LIMIT 0;
DEBUG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:

id | val
----+-----
(0 rows)

-- all hint types together
/+ SeqScan(t1) MergeJoin(t1 t2) Leading(t1 t2) Rows(t1 t2 +10) Parallel(t1 8 hard) Set(random_page_cost 2.0)/
EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
DEBUG: adjusted rows 1000 to 1010
DEBUG: pg_hint_plan:
used hint:
SeqScan(t1)
MergeJoin(t1 t2)
Leading(t1 t2)
Set(random_page_cost 2.0)
Rows(t1 t2 +10)
Parallel(t1 8 hard)
not used hint:
duplication hint:
error hint:

               QUERY PLAN

Nested Loop
-> Merge Join
Merge Cond: (t2.id = t1.id)
-> Index Scan using t2_pkey on t2
-> Sort
Sort Key: t1.id
-> Gather
Workers Planned: 8
-> Parallel Seq Scan on t1
-> Index Scan using t3_pkey on t3
Index Cond: (id = t1.id)
(11 rows)
```

文章转载自digoal，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL hint pg_hint_plan 的详细用法

作者

日期

标签

背景

支持哪些hint

define BLOCK_COMMENT_START "/*"

define BLOCK_COMMENT_END "*/"

define HINT_COMMENT_KEYWORD "+"

define HINT_START BLOCK_COMMENT_START HINT_COMMENT_KEYWORD

define HINT_END BLOCK_COMMENT_END

define HINT_SEQSCAN "SeqScan"

define HINT_INDEXSCAN "IndexScan"

define HINT_INDEXSCANREGEXP "IndexScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_BITMAPSCAN "BitmapScan"

define HINT_BITMAPSCANREGEXP "BitmapScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_TIDSCAN "TidScan"

define HINT_NOSEQSCAN "NoSeqScan"

define HINT_NOINDEXSCAN "NoIndexScan"

define HINT_NOBITMAPSCAN "NoBitmapScan"

define HINT_NOTIDSCAN "NoTidScan"

define HINT_INDEXONLYSCAN "IndexOnlyScan"

define HINT_INDEXONLYSCANREGEXP "IndexOnlyScanRegexp" 使用正则表示要用哪些索引, 居然没有不使用哪些索引, 也许未来会支持

define HINT_NOINDEXONLYSCAN "NoIndexOnlyScan"

define HINT_PARALLEL "Parallel"

define HINT_NESTLOOP "NestLoop" JOIN方法

define HINT_MERGEJOIN "MergeJoin" JOIN方法

define HINT_HASHJOIN "HashJoin" JOIN方法

define HINT_NONESTLOOP "NoNestLoop"

define HINT_NOMERGEJOIN "NoMergeJoin"

define HINT_NOHASHJOIN "NoHashJoin"

define HINT_LEADING "Leading" JOIN的驱动顺序

define HINT_SET "Set" 设置参数

define HINT_ROWS "Rows" 设置评估为多少行

用法

?column?

?column?

?column?

?column?

?column?

PostgreSQL 许愿链接

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

评论