DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}');
ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
-\! pg_ctl reload
-server signaled
+SELECT pg_reload_conf();
+ pg_reload_conf
+----------------
+ t
+(1 row)
+
SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
+SET max_parallel_workers_per_gather TO 0;
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
QUERY PLAN
---------------------
-- No. S-1-5-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (c1 = 1)
- -> Seq Scan on p1c1
+ -> Seq Scan on p1c1 p1_2
Filter: (c1 = 1)
(5 rows)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Append
- -> Index Scan using p1_i on p1
+ -> Index Scan using p1_i on p1 p1_1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1
+ -> Index Scan using p1c1_i on p1c1 p1_2
Index Cond: (c1 = 1)
(5 rows)
-- No. S-1-5-4
CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1;
- QUERY PLAN
-----------------------------------
- Index Scan using tm1_pkey on tm1
+ QUERY PLAN
+------------------------------------
+ Index Scan using tm1_c1_idx on tm1
Index Cond: (c1 = 1)
(2 rows)
QUERY PLAN
-------------------------------------------------
Index Scan using pg_class_oid_index on pg_class
- Index Cond: (oid = 1::oid)
+ Index Cond: (oid = '1'::oid)
(2 rows)
/*+SeqScan(pg_class)*/
duplication hint:
error hint:
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------
Seq Scan on pg_class
- Filter: (oid = 1::oid)
+ Filter: (oid = '1'::oid)
(2 rows)
-- No. S-1-5-6
-- No. S-1-5-9
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Only Scan using t1_i1 on t1 t1_1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
/*+SeqScan(c1)*/
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Only Scan using t1_i1 on t1 t1_1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
-- No. S-1-5-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1;
InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Sort Key: b1t2.c1
-> Seq Scan on t2 b1t2
-> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
+ Index Cond: (c1 = b1t1.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(29 rows)
+ Index Cond: (c1 = bmt1.c1)
+(27 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Sort Key: b1t2.c1
-> Seq Scan on t2 b1t2
-> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
+ Index Cond: (c1 = b1t1.c1)
InitPlan 2 (returns $3)
-> Aggregate
-> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
-> Merge Join
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Merge Join
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2
-> Index Only Scan using t4_i1 on t4 b2t4
- Index Cond: (c1 = b2t3.c1)
+ Index Cond: (c1 = b2t1.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(44 rows)
+ Index Cond: (c1 = bmt1.c1)
+(41 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
----------------------------------------------------------------
Aggregate
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(14 rows)
+ Index Cond: (c1 = bmt1.c1)
+(13 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
----------------------------------------------------------------
Aggregate
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(14 rows)
+ Index Cond: (c1 = bmt1.c1)
+(13 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Sort Key: b1t2.c1
-> Seq Scan on t2 b1t2
-> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
+ Index Cond: (c1 = b1t1.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(30 rows)
+ Index Cond: (c1 = bmt1.c1)
+(28 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Sort Key: b1t2.c1
-> Seq Scan on t2 b1t2
-> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
+ Index Cond: (c1 = b1t1.c1)
InitPlan 2 (returns $3)
-> Aggregate
-> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
-> Merge Join
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Merge Join
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2
-> Index Only Scan using t4_i1 on t4 b2t4
- Index Cond: (c1 = b2t3.c1)
+ Index Cond: (c1 = b2t1.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(45 rows)
+ Index Cond: (c1 = bmt1.c1)
+(42 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t3.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1
- -> Index Only Scan using t3_i1 on t3 b1t3
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt2.c1 = bmt1.c1)
-> Nested Loop
-> Hash Join
- Hash Cond: (bmt2.c1 = c1.c1)
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Seq Scan on t2 bmt2
-> Hash
- -> CTE Scan on c1
- -> Index Only Scan using t3_i1 on t3 bmt3
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1
Index Cond: (c1 = bmt2.c1)
- -> Index Only Scan using t1_i1 on t1 bmt1
- Index Cond: (c1 = bmt3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3
+ Index Cond: (c1 = bmt1.c1)
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
-(32 rows)
+ Index Cond: (c1 = bmt1.c1)
+(27 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Hash Join
- Hash Cond: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t1 b1t1
- -> Hash
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4
-> Hash
-> Nested Loop
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
-> Bitmap Heap Scan on t3 bmt3
Recheck Cond: (c1 = bmt1.c1)
-> Bitmap Index Scan on t3_pkey
Index Cond: (c1 = bmt1.c1)
-(34 rows)
+(36 rows)
-- No. S-2-1-8
EXPLAIN (COSTS false)
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t3.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1
- -> Index Only Scan using t3_i1 on t3 b1t3
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t3.c1)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t3.c1)
- -> Index Only Scan using t1_i1 on t1 b2t1
- -> Index Only Scan using t3_i1 on t3 b2t3
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
- -> Index Only Scan using t4_i1 on t4 b2t4
- Index Cond: (c1 = b2t3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt2.c1 = bmt1.c1)
-> Nested Loop
-> Hash Join
- Hash Cond: (bmt2.c1 = c1.c1)
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Seq Scan on t2 bmt2
-> Hash
- -> CTE Scan on c1
- -> Index Only Scan using t3_i1 on t3 bmt3
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1
Index Cond: (c1 = bmt2.c1)
- -> Index Only Scan using t1_i1 on t1 bmt1
- Index Cond: (c1 = bmt3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3
+ Index Cond: (c1 = bmt1.c1)
-> Index Only Scan using t4_i1 on t4 bmt4
- Index Cond: (c1 = bmt3.c1)
- -> CTE Scan on c2
-(50 rows)
+ Index Cond: (c1 = bmt1.c1)
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ -> Index Only Scan using t3_i1 on t3 b2t3
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t1.c1)
+(42 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Hash Join
- Hash Cond: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t1 b1t1
- -> Hash
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
- CTE c2
- -> Aggregate
- -> Hash Join
- Hash Cond: (b2t3.c1 = b2t1.c1)
- -> Seq Scan on t3 b2t3
- -> Hash
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- -> Index Scan using t4_pkey on t4 b2t4
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t4.c1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4
-> Hash
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
-> Nested Loop
-> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
- -> CTE Scan on c2
-(54 rows)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Seq Scan on t1 bmt1
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(53 rows)
----
---- No. S-2-2 the number of the tables per quiry block
QUERY PLAN
-----------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
-(27 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+(20 rows)
/*+SeqScan(bmt1)
TidScan(b1t1)
QUERY PLAN
---------------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
- -> Result
- InitPlan 3 (returns $2)
- -> Limit
-> Bitmap Heap Scan on t1 b2t1
Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 5 (returns $4)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Scan using t1_pkey on t1 b3t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
- -> CTE Scan on c1
-(28 rows)
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Result
+(21 rows)
-- No. S-2-2-2
EXPLAIN (COSTS false)
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)'
)
;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(34 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(32 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)
TidScan(b1t1)SeqScan(b1t2)
QUERY PLAN
--------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $2)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
-> Tid Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (c1 = b2t2.c1)
- InitPlan 3 (returns $4)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Nested Loop
-> Index Scan using t1_pkey on t1 b3t1
Index Cond: (c1 = b3t1.c1)
-> Nested Loop
-> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid))
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
-> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(37 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(35 rows)
-- No. S-2-2-3
EXPLAIN (COSTS false)
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)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
-> Tid Scan on t4 b3t4
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(67 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
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(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:
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Nested Loop
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t1.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t1.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t1.c1)
- InitPlan 2 (returns $4)
+ InitPlan 1 (returns $2)
-> Aggregate
-> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
+ Join Filter: (b2t2.c1 = b2t1.c1)
-> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
-> Tid Scan on t2 b2t2
TID Cond: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t2.c1)
+ -> Seq Scan on t3 b2t3
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t2.c1)
- -> Seq Scan on t3 b2t3
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
-> Index Scan using t4_pkey on t4 b2t4
- Index Cond: (c1 = b2t1.c1)
+ Index Cond: (c1 = b2t2.c1)
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $7)
+ InitPlan 2 (returns $5)
-> Aggregate
-> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
+ Join Filter: (b3t1.c1 = b3t2.c1)
-> Nested Loop
+ Join Filter: (b3t3.c1 = b3t1.c1)
-> Nested Loop
+ Join Filter: (b3t3.c1 = b3t4.c1)
-> Tid Scan on t3 b3t3
TID Cond: (ctid = '(1,1)'::tid)
- -> Index Scan using t1_pkey on t1 b3t1
- Index Cond: (c1 = b3t3.c1)
+ -> Seq Scan on t4 b3t4
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t2 b3t2
- Recheck Cond: (c1 = b3t1.c1)
+ -> Index Scan using t1_pkey on t1 b3t1
+ Index Cond: (c1 = b3t4.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = b3t1.c1)
- -> Seq Scan on t4 b3t4
+ -> Bitmap Heap Scan on t2 b3t2
+ Recheck Cond: (c1 = b3t3.c1)
Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = b3t3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $7) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
+ Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt2.c1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(74 rows)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(80 rows)
-- No. S-2-2-4
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(46 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
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(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:
TidScan(bmt4)
not used hint:
duplication hint:
-error hint:
-
- QUERY PLAN
---------------------------------------------------------------------------------
- Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Nested Loop
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t1.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t1.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t1.c1)
- InitPlan 3 (returns $3)
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $2)
+ InitPlan 1 (returns $0)
-> Limit
-> Bitmap Heap Scan on t1 b2t1
Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $5)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Scan Backward using t1_pkey on t1 b3t1
Index Cond: (c1 IS NOT NULL)
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt2.c1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(54 rows)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(56 rows)
----
---- No. S-2-3 RULE or VIEW
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
-(20 rows)
+ Index Cond: (c1 = t2.c1)
+(22 rows)
EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
QUERY PLAN
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Seq Scan on t2 b1t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t1.c1)
+ Index Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t1.c1)
+ Recheck Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t1.c1)
-(20 rows)
+ Index Cond: (c1 = b1t2.c1)
+(22 rows)
-- No. S-2-3-2
EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
-(41 rows)
+ Index Cond: (c1 = t2.c1)
+(45 rows)
EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
QUERY PLAN
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Seq Scan on t2 b1t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t1.c1)
+ Index Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t1.c1)
+ Recheck Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t1.c1)
+ Index Cond: (c1 = b1t2.c1)
Aggregate
-> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
+ Join Filter: (b2t2.c1 = b2t1.c1)
-> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
-> Nested Loop
-> Seq Scan on r2_
Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
-> Tid Scan on t2 b2t2
TID Cond: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t2.c1)
+ -> Seq Scan on t3 b2t3
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t2.c1)
- -> Seq Scan on t3 b2t3
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
-> Index Scan using t4_pkey on t4 b2t4
- Index Cond: (c1 = b2t1.c1)
+ Index Cond: (c1 = b2t2.c1)
Filter: (ctid = '(1,1)'::tid)
-(41 rows)
+(45 rows)
-- No. S-2-3-3
EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Aggregate
-> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3
- Index Cond: (c1 = t1.c1)
+ Index Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4
- Recheck Cond: (c1 = t1.c1)
+ Recheck Cond: (c1 = t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = t1.c1)
-(62 rows)
+ Index Cond: (c1 = t2.c1)
+(68 rows)
EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
QUERY PLAN
------------------------------------------------------------------------------
Aggregate
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Seq Scan on t2 b1t2
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t1.c1)
+ Index Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t1.c1)
+ Recheck Cond: (c1 = b1t2.c1)
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t1.c1)
+ Index Cond: (c1 = b1t2.c1)
Aggregate
-> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
+ Join Filter: (b2t2.c1 = b2t1.c1)
-> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
-> Nested Loop
-> Seq Scan on r3_
Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
-> Tid Scan on t2 b2t2
TID Cond: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t2.c1)
+ -> Seq Scan on t3 b2t3
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t2.c1)
- -> Seq Scan on t3 b2t3
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
-> Index Scan using t4_pkey on t4 b2t4
- Index Cond: (c1 = b2t1.c1)
+ Index Cond: (c1 = b2t2.c1)
Filter: (ctid = '(1,1)'::tid)
Aggregate
-> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
+ Join Filter: (b3t1.c1 = b3t2.c1)
-> Nested Loop
+ Join Filter: (b3t3.c1 = b3t1.c1)
-> Nested Loop
+ Join Filter: (b3t3.c1 = b3t4.c1)
-> Nested Loop
-> Seq Scan on r3_
Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
-> Tid Scan on t3 b3t3
TID Cond: (ctid = '(1,1)'::tid)
- -> Index Scan using t1_pkey on t1 b3t1
- Index Cond: (c1 = b3t3.c1)
+ -> Seq Scan on t4 b3t4
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t2 b3t2
- Recheck Cond: (c1 = b3t1.c1)
+ -> Index Scan using t1_pkey on t1 b3t1
+ Index Cond: (c1 = b3t4.c1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = b3t1.c1)
- -> Seq Scan on t4 b3t4
+ -> Bitmap Heap Scan on t2 b3t2
+ Recheck Cond: (c1 = b3t3.c1)
Filter: (ctid = '(1,1)'::tid)
-(62 rows)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = b3t3.c1)
+(68 rows)
-- No. S-2-3-4
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
----
-- No. S-2-4-1
EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
- QUERY PLAN
----------------------------
- Values Scan on "*VALUES*"
- Filter: (column1 = 1)
-(2 rows)
+ QUERY PLAN
+------------
+ Result
+(1 row)
/*+SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
duplication hint:
error hint:
- QUERY PLAN
----------------------------
- Values Scan on "*VALUES*"
- Filter: (column1 = 1)
-(2 rows)
+ QUERY PLAN
+------------
+ Result
+(1 row)
/*+SeqScan(*VALUES*)*/
EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
duplication hint:
error hint:
- QUERY PLAN
----------------------------
- Values Scan on "*VALUES*"
- Filter: (column1 = 1)
-(2 rows)
+ QUERY PLAN
+------------
+ Result
+(1 row)
-- No. S-2-4-2
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------------------------------------
- Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*_1"
-(4 rows)
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
/*+SeqScan(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near ""
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: SeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
error hint:
SeqScan(t1 t2)
- QUERY PLAN
-------------------------------------------------------------
- Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*_1"
-(4 rows)
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
/*+SeqScan(*VALUES*)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------
- Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*_1"
-(4 rows)
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
----
---- No. S-3-1 scan method hint
---- No. S-3-4 index type
----
\d s1.ti1
- Table "s1.ti1"
- Column | Type | Modifiers
---------+---------+-----------
- c1 | integer | not null
- c2 | integer |
- c3 | integer |
- c4 | text |
+ Table "s1.ti1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | not null |
+ c2 | integer | | |
+ c3 | integer | | |
+ c4 | text | | |
Indexes:
"ti1_pkey" PRIMARY KEY, btree (c1)
- "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2)
- "ti1_uniq" UNIQUE, btree (c1)
"ti1_btree" btree (c1)
+ "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2)
"ti1_expr" btree ((c1 < 100))
"ti1_gin" gin (c1)
"ti1_gist" gist (c1)
"ti1_multi" btree (c1, c2, c3, c4)
"ti1_pred" btree (lower(c4))
"ti1_ts" gin (to_tsvector('english'::regconfig, c4))
+ "ti1_uniq" UNIQUE, btree (c1)
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)';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Tid Scan on ti1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-1
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_btree on ti1
Index Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-2
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_hash on ti1
Index Cond: (c1 = 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-3
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_gist on ti1
Index Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-4
-----------------------------------------------------------------------------------------------------------------------------------------
Tid Scan on ti1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-5
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_expr on ti1
Index Cond: ((c1 < 100) = true)
- Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-6
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_uniq on ti1
Index Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-8
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_multi on ti1
Index Cond: ((c1 < 100) AND (c2 = 1))
- Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-9
-----------------------------------------------------------------------------------------------------------------------------------------
Tid Scan on ti1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-10
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_pkey on ti1
Index Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-11
------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_c2_key on ti1
Index Cond: (c2 = 1)
- Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-12
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_btree
Index Cond: (c1 < 100)
(5 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 = 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_hash
Index Cond: (c1 = 100)
(5 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_gist
Index Cond: (c1 < 100)
(5 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_gin
Index Cond: (c1 < 100)
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
- Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_expr
Index Cond: ((c1 < 100) = true)
(4 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_uniq
Index Cond: (c1 < 100)
(5 rows)
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: ((c1 < 100) AND (c2 = 1))
- Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_multi
Index Cond: ((c1 < 100) AND (c2 = 1))
(5 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_pkey
Index Cond: (c1 < 100)
(5 rows)
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ti1
Recheck Cond: (c2 = 1)
- Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
-> Bitmap Index Scan on ti1_c2_key
Index Cond: (c2 = 1)
(5 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------
- Index Scan using ti1_gist on ti1
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_gist on ti1
Index Cond: (c1 < 1)
(2 rows)
-----------------------------------
Index Scan using ti1_expr on ti1
Index Cond: ((c1 < 100) = true)
- Filter: (c1 < 100)
-(3 rows)
+(2 rows)
-- No. S-3-4-28
EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
---- No. S-3-5 not used index
----
-- No. S-3-5-1
-\! psql contrib_regression -c "/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-2
-\! psql contrib_regression -c "/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-3
-\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-4
-\! psql contrib_regression -c "/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-5
-\! psql contrib_regression -c "/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-6
-\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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:
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
-(3 rows)
-- No. S-3-5-7
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Index Cond: (c1 = 1)
(2 rows)
-\! psql contrib_regression -c "/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
used hint:
TidScan(t1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------
- Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 1)
-(3 rows)
----
---- No. S-3-6 query structure
SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
QUERY PLAN
---------------------------------------------------------------------
- Result
+ Aggregate
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan using t1_i1 on t1 b4t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
- -> Limit
- -> Index Only Scan using t1_i1 on t1 b3t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = $3))
-(17 rows)
+ -> Index Only Scan using t1_i1 on t1 b3t1
+ Index Cond: (c1 = $3)
+(15 rows)
/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
*/
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)
QUERY PLAN
---------------------------------------------------------------------
- Result
+ Aggregate
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Seq Scan on t1 b4t1
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
- -> Limit
- -> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = $3))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((c1 IS NOT NULL) AND (c1 = $3))
-(19 rows)
+ -> Bitmap Heap Scan on t1 b3t1
+ Recheck Cond: (c1 = $3)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = $3)
+(17 rows)
-- No. S-3-7-2
EXPLAIN (COSTS false)
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);
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Index Only Scan using t1_i1 on t1 b1t1
- Index Cond: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b2t1
Index Cond: (c1 = 1)
-> Seq Scan on t2 b2t2
Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b4t1
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b3t1
- Index Cond: (c1 = $2)
+ Index Cond: (c1 = $1)
-> Seq Scan on t2 b3t2
- Filter: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(30 rows)
+ Filter: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey)
QUERY PLAN
---------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t1 b1t1
- Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
-> Index Scan using t1_pkey on t1 b2t1
Index Cond: (c1 = 1)
-> Seq Scan on t2 b2t2
Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
-> Seq Scan on t1 b4t1
-> Nested Loop
-> Nested Loop
-> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: (c1 = $2)
+ Recheck Cond: (c1 = $1)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = $2)
+ Index Cond: (c1 = $1)
-> Index Scan using t2_pkey on t2 b3t2
- Index Cond: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(34 rows)
+ Index Cond: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(32 rows)
-- No. S-3-7-3
EXPLAIN (COSTS false)
QUERY PLAN
---------------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Index Only Scan using t1_i1 on t1 b1t1
- Index Cond: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan using t1_i1 on t1 b2t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Only Scan using t1_i1 on t1 b4t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b3t1
- Index Cond: (c1 = $4)
+ Index Cond: (c1 = $3)
-> Seq Scan on t2 b3t2
- Filter: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(28 rows)
+ Filter: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(26 rows)
/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
TidScan(b1t2)IndexScan(b3t2 t2_pkey)
QUERY PLAN
---------------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t1 b1t1
- Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Index Scan using t1_pkey on t1 b2t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $2)
-> Limit
-> Seq Scan on t1 b4t1
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Nested Loop
-> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: (c1 = $4)
+ Recheck Cond: (c1 = $3)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = $4)
+ Index Cond: (c1 = $3)
-> Index Scan using t2_pkey on t2 b3t2
- Index Cond: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(30 rows)
+ Index Cond: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
----
---- No. S-3-8 inheritance table select/update type
-- No. S-3-8-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (c1 = 1)
- -> Seq Scan on p1c1
+ -> Seq Scan on p1c1 p1_2
Filter: (c1 = 1)
(5 rows)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Append
- -> Index Scan using p1_i on p1
+ -> Index Scan using p1_i on p1 p1_1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1
+ -> Index Scan using p1c1_i on p1c1 p1_2
Index Cond: (c1 = 1)
(5 rows)
-- No. S-3-8-4
EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Update on p1
+ Update on p1
+ Update on p1c1 p1_1
-> Seq Scan on p1
Filter: (c1 = 1)
- -> Seq Scan on p1c1
+ -> Seq Scan on p1c1 p1_1
Filter: (c1 = 1)
-(5 rows)
+(7 rows)
/*+IndexScan(p1)*/
EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Update on p1
+ Update on p1
+ Update on p1c1 p1_1
-> Index Scan using p1_i on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1
+ -> Index Scan using p1c1_i on p1c1 p1_1
Index Cond: (c1 = 1)
-(5 rows)
+(7 rows)
/*+IndexScan(p1 p1_pkey)*/
EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
LOG: available indexes for IndexScan(p1): p1_pkey
LOG: available indexes for IndexScan(p1c1): p1c1_pkey
-LOG: available indexes for IndexScan(p1c2): p1c2_pkey
-LOG: available indexes for IndexScan(p1c3): p1c3_pkey
+LOG: available indexes for IndexScan(p1): p1_pkey
+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p1 p1_pkey)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Update on p1
+ Update on p1
+ Update on p1c1 p1_1
-> Index Scan using p1_pkey on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_pkey on p1c1
+ -> Index Scan using p1c1_pkey on p1c1 p1_1
Index Cond: (c1 = 1)
-(5 rows)
+(7 rows)
----
---- No. S-3-9 inheritance table number
----
-- No. S-3-9-1
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (c1 = 1)
- -> Seq Scan on p1c1
+ -> Seq Scan on p1c1 p1_2
Filter: (c1 = 1)
(5 rows)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Append
- -> Index Scan using p1_i on p1
+ -> Index Scan using p1_i on p1 p1_1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1
+ -> Index Scan using p1c1_i on p1c1 p1_2
Index Cond: (c1 = 1)
(5 rows)
-- No. S-3-9-2
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: (c1 = 1)
- -> Seq Scan on p2c1
+ -> Seq Scan on p2c1 p2_2
Filter: (c1 = 1)
- -> Seq Scan on p2c1c1
+ -> Seq Scan on p2c1c1 p2_3
Filter: (c1 = 1)
(7 rows)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Append
- -> Index Scan using p2_i on p2
+ -> Index Scan using p2_i on p2 p2_1
Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1
+ -> Index Scan using p2c1_i on p2c1 p2_2
Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1
+ -> Index Scan using p2c1c1_i on p2c1c1 p2_3
Index Cond: (c1 = 1)
(7 rows)
---- No. S-3-10 inheritance table specified table
----
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: (c1 = 1)
- -> Seq Scan on p2c1
+ -> Seq Scan on p2c1 p2_2
Filter: (c1 = 1)
- -> Seq Scan on p2c1c1
+ -> Seq Scan on p2c1c1 p2_3
Filter: (c1 = 1)
(7 rows)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Append
- -> Index Scan using p2_i on p2
+ -> Index Scan using p2_i on p2 p2_1
Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1
+ -> Index Scan using p2c1_i on p2c1 p2_2
Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1
+ -> Index Scan using p2c1c1_i on p2c1c1 p2_3
Index Cond: (c1 = 1)
(7 rows)
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-not used hint:
IndexScan(p2c1)
+not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: (c1 = 1)
- -> Seq Scan on p2c1
- Filter: (c1 = 1)
- -> Seq Scan on p2c1c1
+ -> Index Scan using p2c1_i on p2c1 p2_2
+ Index Cond: (c1 = 1)
+ -> Seq Scan on p2c1c1 p2_3
Filter: (c1 = 1)
(7 rows)
-- No. S-3-10-3
-\! psql contrib_regression -c "EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:"
- QUERY PLAN
-----------------------------------------------------------
- Append (cost=0.00..2.75 rows=4 width=10)
- -> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32)
+\o results/ut-S.tmpout
+EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
Filter: ((c1 < 10) AND ((c2 * 2) < 100))
- -> Seq Scan on p1c1 (cost=0.00..2.75 rows=3 width=2)
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=3 width=xxx)
Filter: ((c1 < 10) AND ((c2 * 2) < 100))
-(6 rows)
-\! psql contrib_regression -c "/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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(p1 p1_parent)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------------------
- Append (cost=0.12..20.48 rows=4 width=10)
- -> Index Scan using p1_parent on p1 (cost=0.12..8.15 rows=1 width=32)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Index Scan using p1_parent on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
Filter: ((c2 * 2) < 100)
- -> Index Scan using p1c1_c4_expr_idx on p1c1 (cost=0.14..12.34 rows=3 width=2)
+ -> Index Scan using p1c1_c4_expr_idx on p1c1 p1_2 (cost=xxx..xxx rows=3 width=xxx)
Filter: ((c2 * 2) < 100)
-(6 rows)
-- No. S-3-10-4
-\! psql contrib_regression -c "/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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):
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------------
- Append (cost=0.12..30000000014.89 rows=4 width=4)
- -> Index Scan using p1_i2 on p1 (cost=0.12..8.14 rows=1 width=4)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Index Scan using p1_i2 on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c2 = 1)
- -> Seq Scan on p1c1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ -> Seq Scan on p1c1 p1_2 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c2 = 1)
- -> Seq Scan on p1c2 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ -> Seq Scan on p1c2 p1_3 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c2 = 1)
- -> Seq Scan on p1c3 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ -> Seq Scan on p1c3 p1_4 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c2 = 1)
-(10 rows)
-- No. S-3-10-5
-\! psql contrib_regression -c "/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1" | grep -v "Planning time:"
+\o results/ut-S.tmpout
+/*+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)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------------------
- Append (cost=10000000000.00..20000000009.77 rows=3 width=34)
- -> Seq Scan on p2 (cost=10000000000.00..10000000000.00 rows=1 width=44)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost={inf}..{inf} rows=3 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 1)
- -> Index Scan using p2c1_pkey on p2c1 (cost=0.12..8.14 rows=1 width=44)
+ -> Index Scan using p2c1_pkey on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- -> Seq Scan on p2c1c1 (cost=10000000000.00..10000000001.62 rows=1 width=14)
+ -> Seq Scan on p2c1c1 p2_3 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 1)
-(8 rows)
----
---- No. S-3-12 specified same table
-- No. S-3-12-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)"
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
-- No. S-3-12-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)"
+INFO: pg_hint_plan: 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:
-- No. S-3-12-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)"
+INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
-- No. S-3-12-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)"
+INFO: pg_hint_plan: 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)"
+INFO: pg_hint_plan: 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:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: SeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: SeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: TidScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: TidScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoSeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoSeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoIndexScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoIndexScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoBitmapScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoBitmapScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoTidScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoTidScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-29
/*+NoIndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoIndexOnlyScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
-- No. S-3-13-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 ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NoIndexOnlyScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
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)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
- -> Index Scan using p1_pkey on p1
+ -> Index Scan using p1_pkey on p1 p1_1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_pkey on p1c1
+ -> Index Scan using p1c1_pkey on p1c1 p1_2
Index Cond: (c1 = 1)
(5 rows)
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)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Append
- -> Index Scan using p1_i on p1
+ -> Index Scan using p1_i on p1 p1_1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1
+ -> Index Scan using p1c1_i on p1c1 p1_2
Index Cond: (c1 = 1)
(5 rows)
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)
duplication hint:
error hint:
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (c1 = 1)
- -> Seq Scan on p1c1
+ -> Seq Scan on p1c1 p1_2
Filter: (c1 = 1)
(5 rows)
DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
-\! pg_ctl reload
-server signaled
+SELECT pg_reload_conf();
+ pg_reload_conf
+----------------
+ t
+(1 row)
+
+\! rm results/ut-S.tmpout
OSDN Git Service
