| -- 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); |
| 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); |
| 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)); |
| 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: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:31:59:988403 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| ScanHint: t2[indexes: types:SeqScan] |
| ScanHint: t3[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ---------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Hash Join |
| Hash Cond: (t1.a = t3.a) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| -> Hash |
| -> Dynamic Seq Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (a < 42) |
| -> Hash |
| -> Seq Scan on your_table t2 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (15 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: 2024-05-09 18:32:00:026919 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:076599 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexScan] |
| ScanHint: t3[indexes: types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:104587 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexOnlyScan] |
| ScanHint: t2[indexes:your_amazing_index types:IndexOnlyScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_amazing_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Only Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Only Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:151931 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexOnlyScan] |
| ScanHint: t2[indexes: types:IndexOnlyScan] |
| ScanHint: t3[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:174348 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_bitmap_index types:BitmapScan] |
| ScanHint: t2[indexes:your_bitmap_index types:BitmapScan] |
| ScanHint: t3[indexes:our_bitmap_index types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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: 2024-05-09 18:32:00:197913 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:BitmapScan] |
| ScanHint: t2[indexes: types:BitmapScan] |
| ScanHint: t3[indexes: types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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: 2024-05-09 18:32:00:201143 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:203360 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on your_table t2 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:205875 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ---------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Seq Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:207873 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:209887 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:214527 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes:our_amazing_index types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:217457 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:219464 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexOnlyScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:221444 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:223490 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes:your_amazing_index types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using your_amazing_index on your_table t2 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:225770 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:228330 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes:our_amazing_index types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Only Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:231187 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:233283 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_bitmap_index types:BitmapScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:235299 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:BitmapScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:237452 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes:your_bitmap_index types:BitmapScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:239674 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:BitmapScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:00:242409 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes:our_bitmap_index types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (8 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: 2024-05-09 18:32:00:245088 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (8 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: 2024-05-09 18:32:00:323155 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoSeqScan] |
| ScanHint: t2[indexes: types:NoSeqScan] |
| ScanHint: t3[indexes: types:NoSeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:380306 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoIndexScan] |
| ScanHint: t2[indexes: types:NoIndexScan] |
| ScanHint: t3[indexes: types:NoIndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:421157 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoIndexOnlyScan] |
| ScanHint: t2[indexes: types:NoIndexOnlyScan] |
| ScanHint: t3[indexes: types:NoIndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (16 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: 2024-05-09 18:32:00:494378 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoBitmapScan] |
| ScanHint: t2[indexes: types:NoBitmapScan] |
| ScanHint: t3[indexes: types:NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:498084 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoIndexScan,NoIndexOnlyScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:500417 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:NoIndexScan,NoIndexOnlyScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on your_table t2 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:502964 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:NoIndexScan,NoIndexOnlyScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ---------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Seq Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:505082 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoSeqScan,NoIndexOnlyScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:507171 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA", |
| 2024-05-09 18:32:00:507627 CDT,THD000,ERROR,"Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Falling back to Postgres-based planner because no plan has been computed for required properties in GPORCA |
| 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-based planner |
| (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: 2024-05-09 18:32:00:514016 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:NoSeqScan,NoIndexOnlyScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:516546 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoSeqScan,NoIndexScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:519353 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:NoSeqScan,NoIndexScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:00:522238 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:NoSeqScan,NoIndexScan,NoBitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (5 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: 2024-05-09 18:32:00:524384 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:NoSeqScan,NoIndexScan,NoIndexOnlyScan] |
| not used 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: GPORCA |
| (6 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: 2024-05-09 18:32:00:526690 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t2[indexes: types:NoSeqScan,NoIndexScan,NoIndexOnlyScan] |
| not used 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: GPORCA |
| (6 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: 2024-05-09 18:32:00:529536 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t3[indexes: types:NoSeqScan,NoIndexScan,NoIndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (8 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: 2024-05-09 18:32:00:577390 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| ScanHint: t2[indexes: types:SeqScan] |
| ScanHint: t3[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ---------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Hash Join |
| Hash Cond: (t1.a = t3.a) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| -> Hash |
| -> Dynamic Seq Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (a < 42) |
| -> Hash |
| -> Seq Scan on your_table t2 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (15 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: 2024-05-09 18:32:00:614794 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:664771 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexScan] |
| ScanHint: t3[indexes: types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:693400 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexOnlyScan] |
| ScanHint: t2[indexes:your_amazing_index types:IndexOnlyScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_amazing_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Only Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Only Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:739671 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexOnlyScan] |
| ScanHint: t2[indexes: types:IndexOnlyScan] |
| ScanHint: t3[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t2.a = t3.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Index Only Scan using your_awesome_index on your_table t2 |
| Index Cond: (a < 42) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t2.a) AND (a < 42)) |
| -> Hash |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| Optimizer: GPORCA |
| (14 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: 2024-05-09 18:32:00:762832 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_bitmap_index types:BitmapScan] |
| ScanHint: t2[indexes:your_bitmap_index types:BitmapScan] |
| ScanHint: t3[indexes:our_bitmap_index types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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: 2024-05-09 18:32:00:785778 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:BitmapScan] |
| ScanHint: t2[indexes: types:BitmapScan] |
| ScanHint: t3[indexes: types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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: 2024-05-09 18:32:00:821331 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| ScanHint: t2[indexes: types:SeqScan] |
| ScanHint: t3[indexes: types:SeqScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ---------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Hash Join |
| Hash Cond: (t1.a = t3.a) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| -> Hash |
| -> Dynamic Seq Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Filter: (a < 42) |
| -> Hash |
| -> Seq Scan on your_table t2 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (15 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: 2024-05-09 18:32:00:882675 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: true |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Index Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| -> Index Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t3.a) AND (a < 42)) |
| -> Index Only Scan using your_incredible_index on your_table t2 |
| Index Cond: ((a = t1.a) AND (a < 42)) |
| Optimizer: GPORCA |
| (13 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: 2024-05-09 18:32:00:966741 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexScan] |
| ScanHint: t3[indexes: types:IndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| --------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: true |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Index Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t3.a) AND (a < 42)) |
| -> Index Only Scan using your_incredible_index on your_table t2 |
| Index Cond: ((a = t1.a) AND (a < 42)) |
| Optimizer: GPORCA |
| (13 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: 2024-05-09 18:32:01:007919 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_incredible_index types:IndexOnlyScan] |
| ScanHint: t2[indexes:your_amazing_index types:IndexOnlyScan] |
| ScanHint: t3[indexes:our_amazing_index types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: true |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Index Only Scan on our_amazing_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| -> Index Only Scan using my_incredible_index on my_table t1 |
| Index Cond: ((a = t3.a) AND (a < 42)) |
| -> Index Only Scan using your_amazing_index on your_table t2 |
| Index Cond: ((a = t1.a) AND (a < 42)) |
| Optimizer: GPORCA |
| (13 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: 2024-05-09 18:32:01:076518 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:IndexOnlyScan] |
| ScanHint: t2[indexes: types:IndexOnlyScan] |
| ScanHint: t3[indexes: types:IndexOnlyScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: true |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Index Only Scan on our_awesome_index on our_table t3 |
| Index Cond: (a < 42) |
| Number of partitions to scan: 4 (out of 4) |
| -> Index Only Scan using my_awesome_index on my_table t1 |
| Index Cond: ((a = t3.a) AND (a < 42)) |
| -> Index Only Scan using your_incredible_index on your_table t2 |
| Index Cond: ((a = t1.a) AND (a < 42)) |
| Optimizer: GPORCA |
| (13 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: 2024-05-09 18:32:01:111429 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes:my_bitmap_index types:BitmapScan] |
| ScanHint: t2[indexes:your_bitmap_index types:BitmapScan] |
| ScanHint: t3[indexes:our_bitmap_index types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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: 2024-05-09 18:32:01:146388 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:BitmapScan] |
| ScanHint: t2[indexes: types:BitmapScan] |
| ScanHint: t3[indexes: types:BitmapScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.a = t2.a) |
| -> Nested Loop |
| Join Filter: true |
| -> Dynamic Bitmap Heap Scan on our_table t3 |
| Number of partitions to scan: 4 (out of 4) |
| Recheck Cond: (a < 42) |
| Filter: (a < 42) |
| -> Dynamic Bitmap Index Scan on our_bitmap_index |
| Index Cond: (a < 42) |
| -> Bitmap Heap Scan on my_table t1 |
| Recheck Cond: ((a = t3.a) AND (a < 42)) |
| -> Bitmap Index Scan on my_bitmap_index |
| Index Cond: ((a = t3.a) AND (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: GPORCA |
| (21 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)'; |
| LOG: 2024-05-09 18:32:01:149557 CDT,THD000,NOTICE,"Operator Unsupported plan hint: TidScan not supported", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Operator Unsupported plan hint: TidScan not supported |
| 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-based planner |
| (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)'; |
| LOG: 2024-05-09 18:32:01:152009 CDT,THD000,NOTICE,"Operator Unsupported plan hint: NoTidScan not supported", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Operator Unsupported plan hint: NoTidScan not supported |
| 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-based planner |
| (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: 2024-05-09 18:32:01:153554 CDT,THD000,NOTICE,"Operator Unsupported plan hint: IndexScanRegexp not supported", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Operator Unsupported plan hint: IndexScanRegexp not supported |
| 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: 2024-05-09 18:32:01:155098 CDT,THD000,NOTICE,"Operator Unsupported plan hint: IndexOnlyScanRegexp not supported", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Operator Unsupported plan hint: IndexOnlyScanRegexp not supported |
| 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: 2024-05-09 18:32:01:156601 CDT,THD000,NOTICE,"Operator Unsupported plan hint: BitmapScanRegexp not supported", |
| INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner |
| DETAIL: Operator Unsupported plan hint: BitmapScanRegexp not supported |
| 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: 2024-05-09 18:32:01:159796 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| not used hint: |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:01:162018 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan,NoIndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:01:164158 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan,NoIndexScan] |
| not used hint: |
| ]", |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on my_table t1 |
| Filter: (a < 42) |
| Optimizer: GPORCA |
| (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: 2024-05-09 18:32:01:177439 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| ScanHint: t2[indexes: types:SeqScan] |
| not used 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 |
| Filter: (NOT (a IS NULL)) |
| -> Hash |
| -> Seq Scan on your_table t2 |
| Optimizer: GPORCA |
| (8 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: 2024-05-09 18:32:01:187053 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| ScanHint: t1[indexes: types:SeqScan] |
| ScanHint: t2[indexes: types:SeqScan] |
| not used 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: GPORCA |
| (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: 2024-05-09 18:32:01:189943 CDT,THD000,TRACE,"PlanHint: [ |
| used hint: |
| not used hint: |
| ScanHint: y[indexes: types:SeqScan] |
| ScanHint: z[indexes: types:NoIndexScan] |
| ]", |
| QUERY PLAN |
| -------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (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) |
| -> Index Scan using my_awesome_index on my_table t1 |
| Index Cond: (a < 42) |
| Optimizer: GPORCA |
| (4 rows) |
| |
| RESET client_min_messages; |
| LOG: statement: RESET client_min_messages; |
| RESET pg_hint_plan.debug_print; |
