(2 rows)
----
----- No. S-2-1 complexity query block
+---- No. S-2-1 some complexity query blocks
----
-- No. S-2-1-1
EXPLAIN (COSTS false)
)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = sbmt4.c1;
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(bmt1)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * FROM s1.t3 bmt3) sbmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(bmt1)
SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
)
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
)
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
)
;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
)
;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
(54 rows)
----
----- No. S-2-3 RULE definition table
+---- No. S-2-3 RULE or VIEW
----
-- No. S-2-3-1
EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
SeqScan(r1)*/
EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r1)
/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
SeqScan(r1_)*/
EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
SeqScan(r2)*/
EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r2)
duplication hint:
error hint:
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r2)
BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
SeqScan(r2_)*/
EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
duplication hint:
error hint:
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
LOG: pg_hint_plan:
used hint:
BitmapScan(b2t1 t1_pkey)
/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
SeqScan(r3)*/
EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r3)
duplication hint:
error hint:
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r3)
duplication hint:
error hint:
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(r3)
IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4)
SeqScan(r3_)*/
EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: pg_hint_plan:
used hint:
TidScan(b1t1)
duplication hint:
error hint:
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
LOG: pg_hint_plan:
used hint:
BitmapScan(b2t1 t1_pkey)
duplication hint:
error hint:
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(b3t1 t1_pkey)
Filter: (ctid = '(1,1)'::tid)
(62 rows)
+-- No. S-2-3-4
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1
+(5 rows)
+
+/*+BitmapScan(v1t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(v1t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Index Scan using t1_i1 on t1 v1t1
+ -> Bitmap Heap Scan on t1 v1t1
+ Recheck Cond: (c1 = v1t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = v1t1.c1)
+(6 rows)
+
+-- No. S-2-3-5
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1_.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1_
+(5 rows)
+
+/*+SeqScan(v1t1)BitmapScan(v1t1_)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(v1t1)
+BitmapScan(v1t1_)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 v1t1
+ -> Bitmap Heap Scan on t1 v1t1_
+ Recheck Cond: (c1 = v1t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = v1t1.c1)
+(6 rows)
+
+-- No. S-2-3-6
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r4t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r4t1
+(5 rows)
+
+/*+BitmapScan(r4t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(r4t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Index Scan using t1_i1 on t1 r4t1
+ -> Bitmap Heap Scan on t1 r4t1
+ Recheck Cond: (c1 = r4t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = r4t1.c1)
+(6 rows)
+
+-- No. S-2-3-7
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r5t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r5t1
+(5 rows)
+
+/*+SeqScan(r4t1)BitmapScan(r5t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r4t1)
+BitmapScan(r5t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 r4t1
+ -> Bitmap Heap Scan on t1 r5t1
+ Recheck Cond: (c1 = r4t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = r4t1.c1)
+(6 rows)
+
----
---- No. S-2-4 VALUES clause
----
-- No. S-3-3-1
/*+IndexScan(ti1 ti1_i3)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i3
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_i3)
-- No. S-3-3-2
/*+IndexScan(ti1 ti1_i3 ti1_i2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i3 ti1_i2
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_i3 ti1_i2)
-- No. S-3-3-3
/*+IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
-- No. S-3-3-4
/*+BitmapScan(ti1 ti1_i3)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i3
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_i3)
-- No. S-3-3-5
/*+BitmapScan(ti1 ti1_i3 ti1_i2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i3 ti1_i2
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_i3 ti1_i2)
-- No. S-3-3-6
/*+BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
-- No. S-3-3-7
/*+IndexOnlyScan(ti1 ti1_i3)*/
EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i3
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_i3)
-- No. S-3-3-8
/*+IndexOnlyScan(ti1 ti1_i3 ti1_i2)*/
EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i3 ti1_i2
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_i3 ti1_i2)
-- No. S-3-3-9
/*+IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
-- No. S-3-4-1
/*+IndexScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_btree
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_btree)
-- No. S-3-4-2
/*+IndexScan(ti1 ti1_hash)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_hash
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_hash)
-- No. S-3-4-3
/*+IndexScan(ti1 ti1_gist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_gist
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_gist)
-- No. S-3-4-4
/*+IndexScan(ti1 ti1_gin)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_gin
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_gin)
-- No. S-3-4-5
/*+IndexScan(ti1 ti1_expr)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_expr
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_expr)
-- No. S-3-4-6
/*+IndexScan(ti1 ti1_pred)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_pred)
-- No. S-3-4-7
/*+IndexScan(ti1 ti1_uniq)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_uniq
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_uniq)
-- No. S-3-4-8
/*+IndexScan(ti1 ti1_multi)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_multi
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_multi)
-- No. S-3-4-9
/*+IndexScan(ti1 ti1_ts)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_ts
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_ts)
-- No. S-3-4-10
/*+IndexScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_pkey)
-- No. S-3-4-11
/*+IndexScan(ti1 ti1_c2_key)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_c2_key
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_c2_key)
-- No. S-3-4-12
/*+BitmapScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_btree
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_btree)
-- No. S-3-4-13
/*+BitmapScan(ti1 ti1_hash)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_hash
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_hash)
-- No. S-3-4-14
/*+BitmapScan(ti1 ti1_gist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_gist
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_gist)
-- No. S-3-4-15
/*+BitmapScan(ti1 ti1_gin)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_gin
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_gin)
-- No. S-3-4-16
/*+BitmapScan(ti1 ti1_expr)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_expr
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_expr)
-- No. S-3-4-17
/*+BitmapScan(ti1 ti1_pred)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_pred)
-- No. S-3-4-18
/*+BitmapScan(ti1 ti1_uniq)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_uniq
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_uniq)
-- No. S-3-4-19
/*+BitmapScan(ti1 ti1_multi)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_multi
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_multi)
-- No. S-3-4-20
/*+BitmapScan(ti1 ti1_ts)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_ts
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_ts)
-- No. S-3-4-10
/*+BitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_pkey)
-- No. S-3-4-11
/*+BitmapScan(ti1 ti1_c2_key)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_c2_key
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_c2_key)
/*+IndexOnlyScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_btree
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_btree)
/*+IndexOnlyScan(ti1 ti1_hash)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_hash
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_hash)
/*+IndexOnlyScan(ti1 ti1_gist)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_gist
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_gist)
/*+IndexOnlyScan(ti1 ti1_gin)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_gin
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_gin)
/*+IndexOnlyScan(ti1 ti1_expr)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_expr
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_expr)
/*+IndexOnlyScan(ti1 ti1_pred)*/
EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_pred)
/*+IndexOnlyScan(ti1 ti1_uniq)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_uniq
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_uniq)
/*+IndexOnlyScan(ti1 ti1_multi)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_multi
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_multi)
/*+IndexOnlyScan(ti1 ti1_ts)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b';
+LOG: available indexes for IndexOnlyScan(ti1): ti1_ts
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_ts)
/*+IndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_pkey)
/*+IndexOnlyScan(ti1 ti1_c2_key)*/
EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_c2_key
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_c2_key)
-- No. S-3-5-1
/*+IndexScan(ti1 ti1_pred)*/
EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_pred)
-- No. S-3-5-2
/*+BitmapScan(ti1 ti1_pred)*/
EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for BitmapScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_pred)
-- No. S-3-5-3
/*+IndexOnlyScan(ti1 ti1_pred)*/
EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_pred)
Filter: (c1 = 100)
(2 rows)
-----
----- No. S-3-6 not exist index
-----
--- No. S-3-6-1
+-- No. S-3-5-4
/*+IndexScan(ti1 not_exist)*/
EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexScan(ti1):
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 not_exist)
Filter: (c1 = 100)
(2 rows)
--- No. S-3-6-2
+-- No. S-3-5-5
/*+BitmapScan(ti1 not_exist)*/
EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for BitmapScan(ti1):
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 not_exist)
Filter: (c1 = 100)
(2 rows)
--- No. S-3-6-3
+-- No. S-3-5-6
/*+IndexOnlyScan(ti1 not_exist)*/
EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexOnlyScan(ti1):
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 not_exist)
Filter: (c1 = 100)
(2 rows)
+-- No. S-3-5-7
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+TidScan(t1)*/
+EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+TidScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+ Filter: (c1 = 1)
+(2 rows)
+
----
----- No. S-3-7 query structure
+---- No. S-3-6 query structure
----
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
QUERY PLAN
TID Cond: (ctid = '(1,1)'::tid)
(6 rows)
--- No. S-3-7-1
+-- No. S-3-6-1
/*+SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE c1 = 100;
LOG: pg_hint_plan:
Filter: (c1 = 100)
(2 rows)
--- No. S-3-7-2
+-- No. S-3-6-2
/*+SeqScan(t1)BitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
LOG: pg_hint_plan:
Index Cond: (c1 = t1.c1)
(7 rows)
--- No. S-3-7-3
+-- No. S-3-6-3
/*+SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
LOG: pg_hint_plan:
(6 rows)
----
----- No. S-3-8 query structure
+---- No. S-3-7 query structure
----
--- No. S-3-8-1
+-- No. S-3-7-1
EXPLAIN (COSTS false)
WITH c1 (c1) AS (
SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1)
SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
) FROM s1.t1 b3t1 WHERE b3t1.c1 = (
SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(b2t1 t1_pkey)
Index Cond: ((c1 IS NOT NULL) AND (c1 = $3))
(19 rows)
--- No. S-3-8-2
+-- No. S-3-7-2
EXPLAIN (COSTS false)
WITH cte1 (c1) AS (
SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b4t2): t2_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t2): t2_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(b1t1)
Filter: (c1 = $2)
(34 rows)
--- No. S-3-8-3
+-- No. S-3-7-3
EXPLAIN (COSTS false)
WITH cte1 (c1) AS (
SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t2): t2_pkey
LOG: pg_hint_plan:
used hint:
SeqScan(b1t1)
(30 rows)
----
----- No. S-3-9 inheritance table select type
+---- No. S-3-8 inheritance table select type
----
--- No. S-3-9-1
+-- No. S-3-8-1
EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
QUERY PLAN
--------------------
Index Cond: (c1 = 1)
(2 rows)
--- No. S-3-9-2
+-- No. S-3-8-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
QUERY PLAN
---------------------------------
(6 rows)
----
----- No. S-3-10 inheritance table number
+---- No. S-3-9 inheritance table number
----
--- No. S-3-10-1
+-- No. S-3-9-1
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
QUERY PLAN
---------------------------------
Index Cond: (c1 = 1)
(6 rows)
--- No. S-3-10-2
+-- No. S-3-9-2
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
QUERY PLAN
-----------------------------------
(8 rows)
----
----- No. S-3-11 inheritance table specified table
+---- No. S-3-10 inheritance table specified table
----
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
QUERY PLAN
Filter: (c1 = 1)
(8 rows)
--- No. S-3-11-1
+-- No. S-3-10-1
/*+IndexScan(p2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
LOG: pg_hint_plan:
Index Cond: (c1 = 1)
(8 rows)
--- No. S-3-11-2
+-- No. S-3-10-2
/*+IndexScan(p2c1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(8 rows)
--- No. S-3-11-3
-/*+IndexScan(p2 p2_pkey p2c1_pkey p2c1c1_pkey)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+-- No. S-3-10-3
+EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Result (cost=0.00..2.75 rows=4 width=10)
+ -> Append (cost=0.00..2.75 rows=4 width=10)
+ -> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32)
+ Filter: ((c1 < 10) AND ((c2 * 2) < 100))
+ -> Seq Scan on p1c1 p1 (cost=0.00..2.75 rows=3 width=2)
+ Filter: ((c1 < 10) AND ((c2 * 2) < 100))
+(6 rows)
+
+/*+IndexScan(p1 p1_parent)*/
+EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+LOG: available indexes for IndexScan(p1): p1_parent
+LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx
+LOG: available indexes for IndexScan(p1c2): p1c2_c4_expr_idx
+LOG: available indexes for IndexScan(p1c3): p1c3_c4_expr_idx
LOG: pg_hint_plan:
used hint:
-IndexScan(p2 p2_pkey p2c1_pkey p2c1c1_pkey)
+IndexScan(p1 p1_parent)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
- Result
- -> Append
- -> Index Scan using p2_pkey on p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1_pkey on p2c1 p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_pkey on p2c1c1 p2
- Index Cond: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Result (cost=0.00..20.73 rows=4 width=10)
+ -> Append (cost=0.00..20.73 rows=4 width=10)
+ -> Index Scan using p1_parent on p1 (cost=0.00..8.27 rows=1 width=32)
+ Filter: ((c2 * 2) < 100)
+ -> Index Scan using p1c1_c4_expr_idx on p1c1 p1 (cost=0.00..12.45 rows=3 width=2)
+ Filter: ((c2 * 2) < 100)
+(6 rows)
--- No. S-3-11-4
+-- No. S-3-10-4
+/*+IndexScan(p1 p1_i2)*/
+EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(p1): p1_i2
+LOG: available indexes for IndexScan(p1c1):
+LOG: available indexes for IndexScan(p1c2):
+LOG: available indexes for IndexScan(p1c3):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_i2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Result (cost=0.00..30000000015.02 rows=4 width=4)
+ -> Append (cost=0.00..30000000015.02 rows=4 width=4)
+ -> Index Scan using p1_i2 on p1 (cost=0.00..8.27 rows=1 width=4)
+ Index Cond: (c2 = 1)
+ -> Seq Scan on p1c1 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ Filter: (c2 = 1)
+ -> Seq Scan on p1c2 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ Filter: (c2 = 1)
+ -> Seq Scan on p1c3 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ Filter: (c2 = 1)
+(10 rows)
+
+-- No. S-3-10-5
/*+IndexScan(p2 p2c1_pkey)*/
EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2c1): p2c1_pkey
+LOG: available indexes for IndexScan(p2c2):
+LOG: available indexes for IndexScan(p2c3):
+LOG: available indexes for IndexScan(p2c1c1):
+LOG: available indexes for IndexScan(p2c1c2):
+LOG: available indexes for IndexScan(p2c2c1):
+LOG: available indexes for IndexScan(p2c2c2):
+LOG: available indexes for IndexScan(p2c3c1):
+LOG: available indexes for IndexScan(p2c3c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2c1_pkey)
(8 rows)
----
----- No. S-3-12 specified same table
+---- No. S-3-11 specified same table
----
--- No. S-3-12-1
+-- No. S-3-11-1
/*+IndexScan(ti1) BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-2
+-- No. S-3-11-2
/*+IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)"
DETAIL: Conflict scan method hint.
+LOG: available indexes for BitmapScan(ti1): ti1_btree
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_btree)
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-3
+-- No. S-3-11-3
/*+BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)"
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-4
+-- No. S-3-11-4
/*+BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)"
DETAIL: Conflict scan method hint.
INFO: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)"
DETAIL: Conflict scan method hint.
+LOG: available indexes for BitmapScan(ti1): ti1_btree
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_btree)
(5 rows)
----
----- No. S-3-13 message output
+---- No. S-3-12 message output
----
--- No. S-3-13-1
+-- No. S-3-12-1
/*+SeqScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
(2 rows)
--- No. S-3-13-2
+-- No. S-3-12-2
/*+SeqScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-3
+-- No. S-3-12-3
/*+SeqScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-4
+-- No. S-3-12-4
/*+IndexScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-13-5
+-- No. S-3-12-5
/*+IndexScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_pkey)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-13-6
+-- No. S-3-12-6
/*+IndexScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_btree ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(ti1 ti1_pkey ti1_btree)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-13-7
+-- No. S-3-12-7
/*+BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-13-8
+-- No. S-3-12-8
/*+BitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_pkey)
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-13-9
+-- No. S-3-12-9
/*+BitmapScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_btree ti1_pkey
LOG: pg_hint_plan:
used hint:
BitmapScan(ti1 ti1_pkey ti1_btree)
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-13-10
+-- No. S-3-12-10
/*+TidScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-11
+-- No. S-3-12-11
/*+TidScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-12
+-- No. S-3-12-12
/*+TidScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-13
+-- No. S-3-12-13
/*+NoSeqScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-14
+-- No. S-3-12-14
/*+NoSeqScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-15
+-- No. S-3-12-15
/*+NoSeqScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-16
+-- No. S-3-12-16
/*+NoIndexScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-17
+-- No. S-3-12-17
/*+NoIndexScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-18
+-- No. S-3-12-18
/*+NoIndexScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-19
+-- No. S-3-12-19
/*+NoBitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-20
+-- No. S-3-12-20
/*+NoBitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-21
+-- No. S-3-12-21
/*+NoBitmapScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-22
+-- No. S-3-12-22
/*+NoTidScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-13-23
+-- No. S-3-12-23
/*+NoTidScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-24
+-- No. S-3-12-24
/*+NoTidScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-13-25
+-- No. S-3-12-25
/*+IndexOnlyScan(ti1)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
LOG: pg_hint_plan:
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-13-26
+-- No. S-3-12-26
/*+IndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_pkey)
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-13-27
+-- No. S-3-12-27
/*+IndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_btree ti1_pkey
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(ti1 ti1_pkey ti1_btree)
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-13-28
+-- No. S-3-12-28
/*+NoIndexOnlyScan(ti1)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
LOG: pg_hint_plan:
Index Cond: (c1 = 1)
(2 rows)
--- No. S-3-13-29
+-- No. S-3-12-29
/*+NoIndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
INFO: hint syntax error at or near ""
Index Cond: (c1 = 1)
(2 rows)
--- No. S-3-13-30
+-- No. S-3-12-30
/*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
INFO: hint syntax error at or near ""
Index Cond: (c1 = 1)
(2 rows)
+----
+---- No. S-3-13 message output
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i2 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-13-1
+/*+IndexScanRegexp(ti1 ti1_.*_key)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(ti1 ti1_.*_key)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Index Scan using ti1_c2_key on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-13-2
+/*+IndexScanRegexp(ti1 ti1_i.)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(ti1 ti1_i.)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i2 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-13-3
+/*+IndexScanRegexp(ti1 no.*_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScanRegexp(ti1):
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(ti1 no.*_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on ti1
+ Filter: (c2 = 1)
+(2 rows)
+
+-- No. S-3-13-4
+/*+IndexScanRegexp(p1 .*pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+LOG: available indexes for IndexScanRegexp(p1): p1_pkey
+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey
+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey
+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 .*pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------
+ Result
+ -> Append
+ -> Index Scan using p1_pkey on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_pkey on p1c1 p1
+ Index Cond: (c1 = 1)
+(6 rows)
+
+-- No. S-3-13-5
+/*+IndexScanRegexp(p1 p1.*i)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx
+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 p1.*i)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------
+ Result
+ -> Append
+ -> Index Scan using p1_i on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1
+ Index Cond: (c1 = 1)
+(6 rows)
+
+-- No. S-3-13-6
+/*+IndexScanRegexp(p1 no.*_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+LOG: available indexes for IndexScanRegexp(p1):
+LOG: available indexes for IndexScanRegexp(p1c1):
+LOG: available indexes for IndexScanRegexp(p1c2):
+LOG: available indexes for IndexScanRegexp(p1c3):
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 no.*_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Result
+ -> Append
+ -> Seq Scan on p1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1
+ Filter: (c1 = 1)
+(6 rows)
+
+----
+---- No. S-3-14 message output
+----
+-- No. S-3-14-1
+/*+IndexScan(ti1 ti1_i1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1): ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-14-2
+/*+IndexScan(ti1 not_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 not_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on ti1
+ Filter: (c2 = 1)
+(2 rows)
+
+-- No. S-3-14-3
+/*+IndexScan(ti1 ti1_i1 ti1_i2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i1 ti1_i2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i2 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-14-4
+/*+IndexScan(ti1 ti1_i1 not_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1): ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i1 not_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
+
+-- No. S-3-14-5
+/*+IndexScan(ti1 not_exist1 not_exist2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 not_exist1 not_exist2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on ti1
+ Filter: (c2 = 1)
+(2 rows)
+