Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / planhints.out
blob: c6fe1b1601030016551cdc100a82a7b0b65f8c7b [file] [log] [blame]
-- start_matchignore
-- m/^LOG.*Missing statistics for column.*/
-- end_matchignore
-- Test Optimizer Plan Hints Feature
--
-- Purpose: Test that plan hints may be used to coerce the plan shape generated
-- by the optimizer.
LOAD 'pg_hint_plan';
DROP SCHEMA IF EXISTS planhints CASCADE;
NOTICE: schema "planhints" does not exist, skipping
CREATE SCHEMA planhints;
SET search_path=planhints;
SET optimizer_trace_fallback=on;
-- Setup tables
CREATE TABLE my_table(a int, b int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INDEX my_awesome_index ON my_table(a);
CREATE INDEX my_amazing_index ON my_table(a);
CREATE INDEX my_incredible_index ON my_table(a);
CREATE INDEX my_bitmap_index ON my_table USING bitmap (a);
CREATE TABLE your_table(a int, b int) WITH (appendonly=true);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INDEX your_awesome_index ON your_table(a);
CREATE INDEX your_amazing_index ON your_table(a);
CREATE INDEX your_incredible_index ON your_table(a);
CREATE INDEX your_bitmap_index ON your_table USING bitmap (a);
CREATE TABLE our_table(a int, b int) PARTITION BY RANGE (a) (PARTITION p1 START(0) END(10) EVERY(3));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE INDEX our_awesome_index ON our_table(a);
CREATE INDEX our_amazing_index ON our_table(a);
CREATE INDEX our_incredible_index ON our_table(a);
CREATE INDEX our_bitmap_index ON our_table USING bitmap (a);
ANALYZE my_table;
ANALYZE your_table;
ANALYZE our_table;
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
--------------------------------------------------------------------
--
-- 1. [JOIN] Specific explicit scan type and implicit/explicit index
--
--------------------------------------------------------------------
SET client_min_messages TO log;
SET pg_hint_plan.debug_print TO ON;
LOG: statement: SET pg_hint_plan.debug_print TO ON;
-- Replace timestamp while logging with static string
-- start_matchsubs
-- m/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}:[0-9]{6} [A-Z]{3}/
-- s/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}:[0-9]{6} [A-Z]{3}/YYYY-MM-DD HH:MM:SS:MSMSMS TMZ/
-- end_matchsubs
/*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan
/*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: available indexes for IndexScan(t1): my_incredible_index
LOG: available indexes for IndexScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
/*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
IndexScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: available indexes for IndexOnlyScan(t1): my_incredible_index
LOG: available indexes for IndexOnlyScan(t2): your_amazing_index
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Only Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Index Only Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Index Scan using your_bitmap_index on your_table t2
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: available indexes for BitmapScan(t1): my_bitmap_index
LOG: available indexes for BitmapScan(t2): your_bitmap_index
LOG: available indexes for BitmapScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx3
LOG: pg_hint_plan:
used hint:
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
/*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
--------------------------------------------------------------------
--
-- 2. [SCAN] Specific explicit scan type and implicit/explicit index
--
--------------------------------------------------------------------
/*+
SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
SeqScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
SeqScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: pg_hint_plan:
used hint:
SeqScan(t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: pg_hint_plan:
used hint:
SeqScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
/*+
IndexScan(t1 my_incredible_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexScan(t1 my_incredible_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for IndexScan(t1): my_incredible_index
LOG: pg_hint_plan:
used hint:
IndexScan(t1 my_incredible_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
-----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
--/*+
-- IndexScan(t2 your_amazing_index)
-- */
--EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
--/*+
-- IndexScan(t2)
-- */
--EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
/*+
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: available indexes for IndexScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Index Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
/*+
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: pg_hint_plan:
used hint:
IndexScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
/*+
IndexOnlyScan(t1 my_incredible_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexOnlyScan(t1 my_incredible_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for IndexOnlyScan(t1): my_incredible_index
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1 my_incredible_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Only Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexOnlyScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexOnlyScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
-- CBDB_MERGE_FIXME: ao/aocs table not suport IndexOnlyScan in PG Optimizer(ORCA support it)
-- after we cherry-pick 74246e48ed(Enable index only scan on ao/aocs table)
-- So current case will generate a scan seq(also other IndexOnlyScan(your_table) will).
-- See more details in indxpath.c:L817, The logic is:
--
-- if (!AMHandlerIsAO(rel->amhandler) ||
-- index->amcostestimate == bmcostestimate)
-- add_path(rel, (Path *) ipath, root);
/*+
IndexOnlyScan(t2 your_amazing_index)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
IndexOnlyScan(t2 your_amazing_index)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: available indexes for IndexOnlyScan(t2): your_amazing_index
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t2 your_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
-----------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexOnlyScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
IndexOnlyScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using your_bitmap_index on your_table t2
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Index Only Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
/*+
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
/*+
BitmapScan(t1 my_bitmap_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
BitmapScan(t1 my_bitmap_index)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for BitmapScan(t1): my_bitmap_index
LOG: pg_hint_plan:
used hint:
BitmapScan(t1 my_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(6 rows)
/*+
BitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
BitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(6 rows)
/*+
BitmapScan(t2 your_bitmap_index)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
BitmapScan(t2 your_bitmap_index)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: available indexes for BitmapScan(t2): your_bitmap_index
LOG: pg_hint_plan:
used hint:
BitmapScan(t2 your_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(6 rows)
/*+
BitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
BitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: pg_hint_plan:
used hint:
BitmapScan(t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(6 rows)
/*+
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: available indexes for BitmapScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx3
LOG: pg_hint_plan:
used hint:
BitmapScan(t3 our_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(19 rows)
/*+
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: pg_hint_plan:
used hint:
BitmapScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(19 rows)
--------------------------------------------------------------------
--
-- 3. [JOIN] No scan type
--
--------------------------------------------------------------------
/*+
NoSeqScan(t1)
NoSeqScan(t2)
NoSeqScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
NoSeqScan(t1)
NoSeqScan(t2)
NoSeqScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
NoSeqScan(t1)
NoSeqScan(t2)
NoSeqScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Index Scan using your_bitmap_index on your_table t2
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
NoIndexScan(t1)
NoIndexScan(t2)
NoIndexScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
NoIndexScan(t1)
NoIndexScan(t2)
NoIndexScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
NoIndexScan(t2)
NoIndexScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
NoIndexOnlyScan(t1)
NoIndexOnlyScan(t2)
NoIndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
NoIndexOnlyScan(t1)
NoIndexOnlyScan(t2)
NoIndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
NoIndexOnlyScan(t1)
NoIndexOnlyScan(t2)
NoIndexOnlyScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
NoBitmapScan(t1)
NoBitmapScan(t2)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: /*+
NoBitmapScan(t1)
NoBitmapScan(t2)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t1.a, t2.a, t3.a FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
NoBitmapScan(t2)
NoBitmapScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
--------------------------------------------------------------------
--
-- 4. [SCAN] No scan type
--
-- Note that pg_hint_plan does not support multiple No.*Scan hints, so the
-- parser will generate warnings indicating conflicting hints.
--
--------------------------------------------------------------------
--
-- Make SeqScan is only valid plan
--
/*+
NoIndexScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoIndexScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
NoIndexScan(t1)
NoIndexOnlyScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoIndexScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
NoIndexScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t2)
not used hint:
duplication hint:
NoIndexScan(t2)
NoIndexOnlyScan(t2)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoIndexScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
NoIndexScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t3)
not used hint:
duplication hint:
NoIndexScan(t3)
NoIndexOnlyScan(t3)
error hint:
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
--
-- Make IndexScan is only valid plan
--
/*+
NoSeqScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoSeqScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t1)
NoIndexOnlyScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
NoSeqScan(t1)
NoIndexOnlyScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
NoSeqScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t2)
NoIndexOnlyScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t2)
not used hint:
duplication hint:
NoSeqScan(t2)
NoIndexOnlyScan(t2)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
NoSeqScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t3)
NoIndexOnlyScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexOnlyScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t3)
not used hint:
duplication hint:
NoSeqScan(t3)
NoIndexOnlyScan(t3)
error hint:
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
--
-- Make IndexOnlyScan is only valid plan
--
/*+
NoSeqScan(t1)
NoIndexScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoSeqScan(t1)
NoIndexScan(t1)
NoBitmapScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t1)
NoIndexScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t1)
NoBitmapScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t1)
not used hint:
duplication hint:
NoSeqScan(t1)
NoIndexScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t2)
NoIndexScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
NoSeqScan(t2)
NoIndexScan(t2)
NoBitmapScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t2)
NoIndexScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t2)
NoBitmapScan(t2)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t2)
not used hint:
duplication hint:
NoSeqScan(t2)
NoIndexScan(t2)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t3)
NoIndexScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
NoSeqScan(t3)
NoIndexScan(t3)
NoBitmapScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t3)
NoIndexScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t3)
NoBitmapScan(t3)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoBitmapScan(t3)
not used hint:
duplication hint:
NoSeqScan(t3)
NoIndexScan(t3)
error hint:
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
--
-- Make BitmapScan is only valid plan
--
/*+
NoSeqScan(t1)
NoIndexScan(t1)
NoIndexOnlyScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoSeqScan(t1)
NoIndexScan(t1)
NoIndexOnlyScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t1)
NoIndexScan(t1)
NoIndexOnlyScan(t1)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t1)
NoIndexOnlyScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoIndexOnlyScan(t1)
not used hint:
duplication hint:
NoSeqScan(t1)
NoIndexScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t2)
NoIndexScan(t2)
NoIndexOnlyScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
LOG: statement: /*+
NoSeqScan(t2)
NoIndexScan(t2)
NoIndexOnlyScan(t2)
*/
EXPLAIN (costs off) SELECT t2.a FROM your_table AS t2 WHERE t2.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t2)
NoIndexScan(t2)
NoIndexOnlyScan(t2)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t2)
NoIndexOnlyScan(t2)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoIndexOnlyScan(t2)
not used hint:
duplication hint:
NoSeqScan(t2)
NoIndexScan(t2)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoSeqScan(t3)
NoIndexScan(t3)
NoIndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
LOG: statement: /*+
NoSeqScan(t3)
NoIndexScan(t3)
NoIndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT t3.a FROM our_table AS t3 WHERE t3.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoSeqScan(t3)
NoIndexScan(t3)
NoIndexOnlyScan(t3)
"
DETAIL: Conflict scan method hint.
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t3)
NoIndexOnlyScan(t3)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoIndexOnlyScan(t3)
not used hint:
duplication hint:
NoSeqScan(t3)
NoIndexScan(t3)
error hint:
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
Optimizer: Postgres query optimizer
(11 rows)
--------------------------------------------------------------------
--
-- 5. [VIEWS] Specific explicit scan type and implicit/explicit index
--
--------------------------------------------------------------------
CREATE VIEW everybody_view AS SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
LOG: statement: CREATE VIEW everybody_view AS SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a WHERE t1.a<42;
/*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
/*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: available indexes for IndexScan(t1): my_incredible_index
LOG: available indexes for IndexScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
/*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
IndexScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: available indexes for IndexOnlyScan(t1): my_incredible_index
LOG: available indexes for IndexOnlyScan(t2): your_amazing_index
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Only Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Index Only Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Index Scan using your_bitmap_index on your_table t2
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: available indexes for BitmapScan(t1): my_bitmap_index
LOG: available indexes for BitmapScan(t2): your_bitmap_index
LOG: available indexes for BitmapScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx3
LOG: pg_hint_plan:
used hint:
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
/*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: statement: /*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) SELECT * FROM everybody_view;
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
--------------------------------------------------------------------
--
-- 6. [CTE] Specific explicit scan type and implicit/explicit index
--
--------------------------------------------------------------------
/*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Seq Scan on our_table_1_prt_p1_1 t3_1
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_2 t3_2
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_3 t3_3
Filter: (a < 42)
-> Seq Scan on our_table_1_prt_p1_4 t3_4
Filter: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on my_table t1
Filter: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
/*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: available indexes for IndexScan(t1): my_incredible_index
LOG: available indexes for IndexScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexScan(t1 my_incredible_index)
IndexScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
-- NB: IndexScan on AO table is invalid because AO tables do not support index
-- scan (e.g. t2)
/*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
IndexScan(t1)
IndexScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: pg_hint_plan:
used hint:
IndexScan(t1)
IndexScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: available indexes for IndexOnlyScan(t1): my_incredible_index
LOG: available indexes for IndexOnlyScan(t2): your_amazing_index
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx2 our_table_1_prt_p1_1_a_idx1 our_table_1_prt_p1_1_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx2 our_table_1_prt_p1_2_a_idx1 our_table_1_prt_p1_2_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx2 our_table_1_prt_p1_3_a_idx1 our_table_1_prt_p1_3_a_idx
LOG: available indexes for IndexOnlyScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx2 our_table_1_prt_p1_4_a_idx1 our_table_1_prt_p1_4_a_idx
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1 my_incredible_index)
IndexOnlyScan(t2 your_amazing_index)
IndexOnlyScan(t3 our_amazing_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Only Scan using our_table_1_prt_p1_1_a_idx2 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_2_a_idx2 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_3_a_idx2 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Only Scan using our_table_1_prt_p1_4_a_idx2 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Index Only Scan using my_incredible_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Seq Scan on your_table t2
Filter: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: pg_hint_plan:
used hint:
IndexOnlyScan(t1)
IndexOnlyScan(t2)
IndexOnlyScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Index Scan using our_table_1_prt_p1_1_a_idx3 on our_table_1_prt_p1_1 t3_1
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_2_a_idx3 on our_table_1_prt_p1_2 t3_2
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_3_a_idx3 on our_table_1_prt_p1_3 t3_3
Index Cond: (a < 42)
-> Index Scan using our_table_1_prt_p1_4_a_idx3 on our_table_1_prt_p1_4 t3_4
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Index Scan using my_bitmap_index on my_table t1
Index Cond: (a < 42)
-> Hash
-> Index Scan using your_bitmap_index on your_table t2
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(23 rows)
/*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: available indexes for BitmapScan(t1): my_bitmap_index
LOG: available indexes for BitmapScan(t2): your_bitmap_index
LOG: available indexes for BitmapScan(our_table_1_prt_p1_1): our_table_1_prt_p1_1_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_2): our_table_1_prt_p1_2_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_3): our_table_1_prt_p1_3_a_idx3
LOG: available indexes for BitmapScan(our_table_1_prt_p1_4): our_table_1_prt_p1_4_a_idx3
LOG: pg_hint_plan:
used hint:
BitmapScan(t1 my_bitmap_index)
BitmapScan(t2 your_bitmap_index)
BitmapScan(t3 our_bitmap_index)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
/*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: statement: /*+
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
*/
EXPLAIN (costs off) WITH cte AS
(
SELECT t1.a AS a1, t2.a AS a2, t3.a AS a3 FROM my_table AS t1 JOIN your_table AS t2 ON t1.a=t2.a JOIN our_table AS t3 ON t3.a=t2.a
)
SELECT a1, a2, a3 FROM cte WHERE a1<42;
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
BitmapScan(t2)
BitmapScan(t3)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Hash Join
Hash Cond: (t3.a = t1.a)
-> Append
Partition Selectors: $0
-> Bitmap Heap Scan on our_table_1_prt_p1_1 t3_1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_1_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_2 t3_2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_2_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_3 t3_3
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_3_a_idx3
Index Cond: (a < 42)
-> Bitmap Heap Scan on our_table_1_prt_p1_4 t3_4
Recheck Cond: (a < 42)
-> Bitmap Index Scan on our_table_1_prt_p1_4_a_idx3
Index Cond: (a < 42)
-> Hash
-> Partition Selector (selector id: $0)
-> Bitmap Heap Scan on my_table t1
Recheck Cond: (a < 42)
-> Bitmap Index Scan on my_bitmap_index
Index Cond: (a < 42)
-> Hash
-> Bitmap Heap Scan on your_table t2
Recheck Cond: (a < 42)
-> Bitmap Index Scan on your_bitmap_index
Index Cond: (a < 42)
Optimizer: Postgres query optimizer
(35 rows)
--------------------------------------------------------------------
--
-- 7. Unsupported hints
--
--------------------------------------------------------------------
/*+
TidScan(t1)
*/
EXPLAIN (costs off) SELECT t1.ctid FROM my_table AS t1 WHERE ctid = '(0,1)';
LOG: statement: /*+
TidScan(t1)
*/
EXPLAIN (costs off) SELECT t1.ctid FROM my_table AS t1 WHERE ctid = '(0,1)';
NOTICE: SELECT uses system-defined column "t1.ctid" without the necessary companion column "t1.gp_segment_id"
HINT: To uniquely identify a row within a distributed table, use the "gp_segment_id" column together with the "ctid" column.
LOG: pg_hint_plan:
used hint:
TidScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Tid Scan on my_table t1
TID Cond: (ctid = '(0,1)'::tid)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoTidScan(t1)
*/
EXPLAIN (costs off) SELECT t1.ctid FROM my_table AS t1 WHERE ctid >= '(0,1)';
LOG: statement: /*+
NoTidScan(t1)
*/
EXPLAIN (costs off) SELECT t1.ctid FROM my_table AS t1 WHERE ctid >= '(0,1)';
NOTICE: SELECT uses system-defined column "t1.ctid" without the necessary companion column "t1.gp_segment_id"
HINT: To uniquely identify a row within a distributed table, use the "gp_segment_id" column together with the "ctid" column.
LOG: pg_hint_plan:
used hint:
NoTidScan(t1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (ctid >= '(0,1)'::tid)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for IndexScanRegexp(t1):
LOG: pg_hint_plan:
used hint:
IndexScanRegexp(t1 '*awesome*')
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
IndexOnlyScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
IndexOnlyScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for IndexOnlyScanRegexp(t1):
LOG: pg_hint_plan:
used hint:
IndexOnlyScanRegexp(t1 '*awesome*')
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
BitmapScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
BitmapScanRegexp(t1 '*awesome*')
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: available indexes for BitmapScanRegexp(t1):
LOG: pg_hint_plan:
used hint:
BitmapScanRegexp(t1 '*awesome*')
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
--------------------------------------------------------------------
--
-- 8. Miscellaneous cases
--
--------------------------------------------------------------------
-- Missing hint relation name argument
/*+
SeqScan()
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
SeqScan()
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "
"
DETAIL: SeqScan hint requires a relation.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
SeqScan()
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
-- Mixing NoIndexScan and SeqScan hints
/*+
SeqScan(t1) NoIndexScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
SeqScan(t1) NoIndexScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "SeqScan(t1) NoIndexScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
NoIndexScan(t1)
not used hint:
duplication hint:
SeqScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
/*+
NoIndexScan(t1) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoIndexScan(t1) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoIndexScan(t1) SeqScan(t1)
"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
not used hint:
duplication hint:
NoIndexScan(t1)
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres query optimizer
(4 rows)
-- Scan Hints with Semi/Anti Semi Joins
/*+
SeqScan(t2) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a, t1.b FROM my_table AS t1 WHERE EXISTS (SELECT 1 FROM your_table AS t2 WHERE t1.a = t2.a);
LOG: statement: /*+
SeqScan(t2) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a, t1.b FROM my_table AS t1 WHERE EXISTS (SELECT 1 FROM your_table AS t2 WHERE t1.a = t2.a);
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Semi Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on my_table t1
-> Hash
-> Seq Scan on your_table t2
Optimizer: Postgres-based planner
(7 rows)
/*+
SeqScan(t2) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a, t1.b FROM my_table AS t1 WHERE NOT EXISTS (SELECT 1 FROM your_table AS t2 WHERE t1.a = t2.a);
LOG: statement: /*+
SeqScan(t2) SeqScan(t1)
*/
EXPLAIN (costs off) SELECT t1.a, t1.b FROM my_table AS t1 WHERE NOT EXISTS (SELECT 1 FROM your_table AS t2 WHERE t1.a = t2.a);
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
SeqScan(t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Anti Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on my_table t1
-> Hash
-> Seq Scan on your_table t2
Optimizer: Postgres-based planner
(7 rows)
-- Missing alias in query to test Un-used Hint logging
/*+
NoIndexScan(z) SeqScan(y)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoIndexScan(z) SeqScan(y)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: pg_hint_plan:
used hint:
not used hint:
SeqScan(y)
NoIndexScan(z)
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres-based planner
(4 rows)
-- Invalid Scan type to test Hint logging behavior
/*+
NoBitmap(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
LOG: statement: /*+
NoBitmap(t1)
*/
EXPLAIN (costs off) SELECT t1.a FROM my_table AS t1 WHERE t1.a<42;
INFO: pg_hint_plan: hint syntax error at or near "NoBitmap(t1)
"
DETAIL: Unrecognized hint keyword "NoBitmap".
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on my_table t1
Filter: (a < 42)
Optimizer: Postgres-based planner
(4 rows)
RESET client_min_messages;
LOG: statement: RESET client_min_messages;
RESET pg_hint_plan.debug_print;