| -- tests index filter with outer refs |
| drop table if exists bfv_tab1; |
| NOTICE: table "bfv_tab1" does not exist, skipping |
| CREATE TABLE bfv_tab1 ( |
| unique1 int4, |
| unique2 int4, |
| two int4, |
| four int4, |
| ten int4, |
| twenty int4, |
| hundred int4, |
| thousand int4, |
| twothousand int4, |
| fivethous int4, |
| tenthous int4, |
| odd int4, |
| even int4, |
| stringu1 name, |
| stringu2 name, |
| string4 name |
| ) distributed by (unique1); |
| create index bfv_tab1_idx1 on bfv_tab1 using btree(unique1); |
| -- GPDB_12_MERGE_FIXME: Non default collation |
| explain select * from bfv_tab1, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) |
| WHERE bfv_tab1.unique1 = v.i and bfv_tab1.stringu1 = v.j; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.06..278.70 rows=14 width=280) |
| -> Hash Join (cost=0.06..278.70 rows=5 width=280) |
| Hash Cond: bfv_tab1.unique1 = "*VALUES*".column1 AND bfv_tab1.stringu1::text = "*VALUES*".column2 |
| -> Seq Scan on bfv_tab1 (cost=0.00..219.00 rows=3967 width=244) |
| -> Hash (cost=0.03..0.03 rows=1 width=36) |
| -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=36) |
| Optimizer status: Postgres query optimizer |
| (7 rows) |
| |
| set gp_enable_relsize_collection=on; |
| -- GPDB_12_MERGE_FIXME: Non default collation |
| explain select * from bfv_tab1, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) |
| WHERE bfv_tab1.unique1 = v.i and bfv_tab1.stringu1 = v.j; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.01..0.09 rows=4 width=280) |
| -> Hash Join (cost=0.01..0.09 rows=2 width=280) |
| Hash Cond: "*VALUES*".column1 = bfv_tab1.unique1 AND "*VALUES*".column2 = bfv_tab1.stringu1::text |
| -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=36) |
| -> Hash (cost=0.00..0.00 rows=1 width=244) |
| -> Seq Scan on bfv_tab1 (cost=0.00..0.00 rows=1 width=244) |
| Settings: gp_enable_relsize_collection=on |
| Optimizer status: Postgres query optimizer |
| (8 rows) |
| |
| -- Test that we do not choose to perform an index scan if indisvalid=false. |
| create table bfv_tab1_with_invalid_index (like bfv_tab1 including indexes); |
| NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table |
| set allow_system_table_mods=on; |
| update pg_index set indisvalid=false where indrelid='bfv_tab1_with_invalid_index'::regclass; |
| reset allow_system_table_mods; |
| explain select * from bfv_tab1_with_invalid_index where unique1>42; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.05 rows=3 width=244) |
| -> Seq Scan on bfv_tab1_with_invalid_index (cost=0.00..0.01 rows=1 width=244) |
| Filter: (unique1 > 42) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| -- Cannot currently upgrade table with invalid index |
| -- (see https://github.com/greenplum-db/gpdb/issues/10805). |
| drop table bfv_tab1_with_invalid_index; |
| reset gp_enable_relsize_collection; |
| --start_ignore |
| DROP TABLE IF EXISTS bfv_tab2_facttable1; |
| NOTICE: table "bfv_tab2_facttable1" does not exist, skipping |
| DROP TABLE IF EXISTS bfv_tab2_dimdate; |
| NOTICE: table "bfv_tab2_dimdate" does not exist, skipping |
| DROP TABLE IF EXISTS bfv_tab2_dimtabl1; |
| NOTICE: table "bfv_tab2_dimtabl1" does not exist, skipping |
| --end_ignore |
| -- Bug-fix verification for MPP-25537: PANIC when bitmap index used in ORCA select |
| CREATE TABLE bfv_tab2_facttable1 ( |
| col1 integer, |
| wk_id smallint, |
| id integer |
| ) |
| with (appendonly=true, orientation=column, compresstype=zlib, compresslevel=5) |
| partition by range (wk_id) ( |
| start (1::smallint) END (20::smallint) inclusive every (1), |
| default partition dflt |
| ) |
| ; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' 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. |
| insert into bfv_tab2_facttable1 select col1, col1, col1 from (select generate_series(1,20) col1)a; |
| CREATE TABLE bfv_tab2_dimdate ( |
| wk_id smallint, |
| col2 date |
| ) |
| ; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'wk_id' 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. |
| insert into bfv_tab2_dimdate select col1, current_date - col1 from (select generate_series(1,20,2) col1)a; |
| CREATE TABLE bfv_tab2_dimtabl1 ( |
| id integer, |
| col2 integer |
| ) |
| ; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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. |
| insert into bfv_tab2_dimtabl1 select col1, col1 from (select generate_series(1,20,3) col1)a; |
| CREATE INDEX idx_bfv_tab2_facttable1 on bfv_tab2_facttable1 (id); |
| --start_ignore |
| set optimizer_analyze_root_partition to on; |
| --end_ignore |
| ANALYZE bfv_tab2_facttable1; |
| ANALYZE bfv_tab2_dimdate; |
| ANALYZE bfv_tab2_dimtabl1; |
| SELECT count(*) |
| FROM bfv_tab2_facttable1 ft, bfv_tab2_dimdate dt, bfv_tab2_dimtabl1 dt1 |
| WHERE ft.wk_id = dt.wk_id |
| AND ft.id = dt1.id; |
| count |
| ------- |
| 4 |
| (1 row) |
| |
| explain SELECT count(*) |
| FROM bfv_tab2_facttable1 ft, bfv_tab2_dimdate dt, bfv_tab2_dimtabl1 dt1 |
| WHERE ft.wk_id = dt.wk_id |
| AND ft.id = dt1.id; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Aggregate (cost=23.80..23.81 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=2.28..23.79 rows=3 width=0) |
| -> Hash Join (cost=2.28..23.74 rows=1 width=0) |
| Hash Cond: (ft.wk_id = dt.wk_id) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1.20..22.65 rows=2 width=2) |
| Hash Key: ft.wk_id |
| -> Hash Join (cost=1.20..22.60 rows=2 width=2) |
| Hash Cond: (ft.id = dt1.id) |
| -> Append (cost=0.00..21.32 rows=21 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_2 ft_1 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_3 ft_2 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_4 ft_3 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_5 ft_4 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_6 ft_5 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_7 ft_6 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_8 ft_7 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_9 ft_8 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_10 ft_9 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_11 ft_10 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_12 ft_11 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_13 ft_12 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_14 ft_13 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_15 ft_14 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_16 ft_15 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_17 ft_16 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_18 ft_17 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_19 ft_18 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_20 ft_19 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_21 ft_20 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_dflt ft_21 (cost=0.00..1.01 rows=1 width=6) |
| -> Hash (cost=1.12..1.12 rows=7 width=4) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..1.12 rows=7 width=4) |
| -> Seq Scan on bfv_tab2_dimtabl1 dt1 (cost=0.00..1.02 rows=2 width=4) |
| -> Hash (cost=1.03..1.03 rows=3 width=2) |
| -> Seq Scan on bfv_tab2_dimdate dt (cost=0.00..1.03 rows=3 width=2) |
| Optimizer: Postgres query optimizer |
| (36 rows) |
| |
| explain SELECT count(*) |
| FROM bfv_tab2_facttable1 ft, bfv_tab2_dimdate dt, bfv_tab2_dimtabl1 dt1 |
| WHERE ft.wk_id = dt.wk_id |
| AND ft.id = dt1.id; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Aggregate (cost=23.80..23.81 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=2.28..23.79 rows=3 width=0) |
| -> Hash Join (cost=2.28..23.74 rows=1 width=0) |
| Hash Cond: (ft.wk_id = dt.wk_id) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1.20..22.65 rows=2 width=2) |
| Hash Key: ft.wk_id |
| -> Hash Join (cost=1.20..22.60 rows=2 width=2) |
| Hash Cond: (ft.id = dt1.id) |
| -> Append (cost=0.00..21.32 rows=21 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_2 ft_1 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_3 ft_2 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_4 ft_3 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_5 ft_4 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_6 ft_5 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_7 ft_6 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_8 ft_7 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_9 ft_8 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_10 ft_9 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_11 ft_10 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_12 ft_11 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_13 ft_12 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_14 ft_13 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_15 ft_14 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_16 ft_15 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_17 ft_16 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_18 ft_17 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_19 ft_18 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_20 ft_19 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_21 ft_20 (cost=0.00..1.01 rows=1 width=6) |
| -> Seq Scan on bfv_tab2_facttable1_1_prt_dflt ft_21 (cost=0.00..1.01 rows=1 width=6) |
| -> Hash (cost=1.12..1.12 rows=7 width=4) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..1.12 rows=7 width=4) |
| -> Seq Scan on bfv_tab2_dimtabl1 dt1 (cost=0.00..1.02 rows=2 width=4) |
| -> Hash (cost=1.03..1.03 rows=3 width=2) |
| -> Seq Scan on bfv_tab2_dimdate dt (cost=0.00..1.03 rows=3 width=2) |
| Optimizer: Postgres query optimizer |
| (36 rows) |
| |
| -- start_ignore |
| create language plpython3u; |
| ERROR: language "plpython3u" already exists |
| -- end_ignore |
| create or replace function count_index_scans(explain_query text) returns int as |
| $$ |
| rv = plpy.execute(explain_query) |
| search_text = 'Index Scan' |
| result = 0 |
| for i in range(len(rv)): |
| cur_line = rv[i]['QUERY PLAN'] |
| if search_text.lower() in cur_line.lower(): |
| result = result+1 |
| return result |
| $$ |
| language plpython3u; |
| DROP TABLE bfv_tab1; |
| DROP TABLE bfv_tab2_facttable1; |
| DROP TABLE bfv_tab2_dimdate; |
| DROP TABLE bfv_tab2_dimtabl1; |
| -- pick index scan when query has a relabel on the index key: non partitioned tables |
| set enable_seqscan = off; |
| -- start_ignore |
| drop table if exists Tab23383; |
| NOTICE: table "tab23383" does not exist, skipping |
| -- end_ignore |
| create table Tab23383(a int, b varchar(20)); |
| 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. |
| insert into Tab23383 select g,g from generate_series(1,1000) g; |
| create index Tab23383_b on Tab23383(b); |
| -- start_ignore |
| select disable_xform('CXformGet2TableScan'); |
| disable_xform |
| --------------------------------- |
| CXformGet2TableScan is disabled |
| (1 row) |
| |
| -- end_ignore |
| select count_index_scans('explain select * from Tab23383 where b=''1'';'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tab23383 where b='1'; |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select count_index_scans('explain select * from Tab23383 where ''1''=b;'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tab23383 where '1'=b; |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select count_index_scans('explain select * from Tab23383 where ''2''> b order by a limit 10;'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tab23383 where '2'> b order by a limit 10; |
| a | b |
| ----+---- |
| 1 | 1 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| (10 rows) |
| |
| select count_index_scans('explain select * from Tab23383 where b between ''1'' and ''2'' order by a limit 10;'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tab23383 where b between '1' and '2' order by a limit 10; |
| a | b |
| ----+---- |
| 1 | 1 |
| 2 | 2 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| (10 rows) |
| |
| -- predicates on both index and non-index key |
| select count_index_scans('explain select * from Tab23383 where b=''1'' and a=''1'';'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tab23383 where b='1' and a='1'; |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| --negative tests: no index scan plan possible, fall back to planner |
| select count_index_scans('explain select * from Tab23383 where b::int=''1'';'); |
| count_index_scans |
| ------------------- |
| 0 |
| (1 row) |
| |
| drop table Tab23383; |
| -- pick index scan when query has a relabel on the index key: partitioned tables |
| -- start_ignore |
| drop table if exists Tbl23383_partitioned; |
| NOTICE: table "tbl23383_partitioned" does not exist, skipping |
| -- end_ignore |
| create table Tbl23383_partitioned(a int, b varchar(20), c varchar(20), d varchar(20)) |
| partition by range(a) |
| (partition p1 start(1) end(500), |
| partition p2 start(500) end(1001)); |
| 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. |
| insert into Tbl23383_partitioned select g,g,g,g from generate_series(1,1000) g; |
| create index idx23383_b on Tbl23383_partitioned(b); |
| -- heterogenous indexes |
| create index idx23383_c on Tbl23383_partitioned_1_prt_p1(c); |
| create index idx23383_cd on Tbl23383_partitioned_1_prt_p2(c,d); |
| set optimizer_enable_dynamictablescan = off; |
| select count_index_scans('explain select * from Tbl23383_partitioned where b=''1'''); |
| count_index_scans |
| ------------------- |
| 2 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where b='1'; |
| a | b | c | d |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| select count_index_scans('explain select * from Tbl23383_partitioned where ''1''=b'); |
| count_index_scans |
| ------------------- |
| 2 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where '1'=b; |
| a | b | c | d |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| select count_index_scans('explain select * from Tbl23383_partitioned where ''2''> b order by a limit 10;'); |
| count_index_scans |
| ------------------- |
| 2 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where '2'> b order by a limit 10; |
| a | b | c | d |
| ----+----+----+---- |
| 1 | 1 | 1 | 1 |
| 10 | 10 | 10 | 10 |
| 11 | 11 | 11 | 11 |
| 12 | 12 | 12 | 12 |
| 13 | 13 | 13 | 13 |
| 14 | 14 | 14 | 14 |
| 15 | 15 | 15 | 15 |
| 16 | 16 | 16 | 16 |
| 17 | 17 | 17 | 17 |
| 18 | 18 | 18 | 18 |
| (10 rows) |
| |
| select count_index_scans('explain select * from Tbl23383_partitioned where b between ''1'' and ''2'' order by a limit 10;'); |
| count_index_scans |
| ------------------- |
| 2 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where b between '1' and '2' order by a limit 10; |
| a | b | c | d |
| ----+----+----+---- |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 10 | 10 | 10 | 10 |
| 11 | 11 | 11 | 11 |
| 12 | 12 | 12 | 12 |
| 13 | 13 | 13 | 13 |
| 14 | 14 | 14 | 14 |
| 15 | 15 | 15 | 15 |
| 16 | 16 | 16 | 16 |
| 17 | 17 | 17 | 17 |
| (10 rows) |
| |
| -- predicates on both index and non-index key |
| select count_index_scans('explain select * from Tbl23383_partitioned where b=''1'' and a=''1'';'); |
| count_index_scans |
| ------------------- |
| 1 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where b='1' and a='1'; |
| a | b | c | d |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| --negative tests: no index scan plan possible, fall back to planner |
| select count_index_scans('explain select * from Tbl23383_partitioned where b::int=''1'';'); |
| count_index_scans |
| ------------------- |
| 0 |
| (1 row) |
| |
| -- heterogenous indexes |
| select count_index_scans('explain select * from Tbl23383_partitioned where c=''1'';'); |
| count_index_scans |
| ------------------- |
| 2 |
| (1 row) |
| |
| select * from Tbl23383_partitioned where c='1'; |
| a | b | c | d |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| -- start_ignore |
| drop table Tbl23383_partitioned; |
| -- end_ignore |
| reset enable_seqscan; |
| -- negative test: due to non compatible cast and CXformGet2TableScan disabled no index plan possible, fallback to planner |
| -- start_ignore |
| drop table if exists tbl_ab; |
| NOTICE: table "tbl_ab" does not exist, skipping |
| -- end_ignore |
| create table tbl_ab(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 idx_ab_b on tbl_ab(b); |
| -- start_ignore |
| select disable_xform('CXformGet2TableScan'); |
| disable_xform |
| --------------------------------- |
| CXformGet2TableScan is disabled |
| (1 row) |
| |
| -- end_ignore |
| explain select * from tbl_ab where b::oid=1; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1176.25 rows=87 width=8) |
| -> Seq Scan on tbl_ab (cost=0.00..1176.25 rows=29 width=8) |
| Filter: b::oid = 1::oid |
| Optimizer status: Postgres query optimizer |
| (4 rows) |
| |
| drop table tbl_ab; |
| drop function count_index_scans(text); |
| -- start_ignore |
| select enable_xform('CXformGet2TableScan'); |
| enable_xform |
| -------------------------------- |
| CXformGet2TableScan is enabled |
| (1 row) |
| |
| -- end_ignore |
| -- |
| -- Check that ORCA can use an index for joins on quals like: |
| -- |
| -- indexkey CMP expr |
| -- expr CMP indexkey |
| -- |
| -- where expr is a scalar expression free of index keys and may have outer |
| -- references. |
| -- |
| create table nestloop_x (i int, j int) distributed by (i); |
| create table nestloop_y (i int, j int) distributed by (i); |
| insert into nestloop_x select g, g from generate_series(1, 20) g; |
| insert into nestloop_y select g, g from generate_series(1, 7) g; |
| create index nestloop_y_idx on nestloop_y (j); |
| -- Coerce the Postgres planner to produce a similar plan. Nested loop joins |
| -- are not enabled by default. And to dissuade it from choosing a sequential |
| -- scan, bump up the cost. enable_seqscan=off won't help, because there is |
| -- no other way to scan table 'x', and once the planner chooses a seqscan for |
| -- one table, it will happily use a seqscan for other tables as well, despite |
| -- enable_seqscan=off. (On PostgreSQL, enable_seqscan works differently, and |
| -- just bumps up the cost of a seqscan, so it would work there.) |
| set seq_page_cost=10000000; |
| set enable_indexscan=on; |
| set enable_nestloop=on; |
| explain select * from nestloop_x as x, nestloop_y as y where x.i + x.j < y.j; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..30030626.55 rows=47 width=16) |
| -> Nested Loop (cost=0.00..30030626.55 rows=16 width=16) |
| -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..30000001.00 rows=20 width=8) |
| -> Seq Scan on nestloop_x x (cost=0.00..30000000.20 rows=7 width=8) |
| -> Index Scan using nestloop_y_idx on nestloop_y y (cost=0.00..510.39 rows=1 width=8) |
| Index Cond: (x.i + x.j) < y.j |
| Settings: enable_indexscan=on; enable_nestloop=on; optimizer=off; seq_page_cost=1e+07 |
| Optimizer status: Postgres query optimizer |
| (8 rows) |
| |
| select * from nestloop_x as x, nestloop_y as y where x.i + x.j < y.j; |
| i | j | i | j |
| ---+---+---+--- |
| 1 | 1 | 3 | 3 |
| 1 | 1 | 4 | 4 |
| 1 | 1 | 5 | 5 |
| 1 | 1 | 6 | 6 |
| 1 | 1 | 7 | 7 |
| 2 | 2 | 5 | 5 |
| 2 | 2 | 6 | 6 |
| 2 | 2 | 7 | 7 |
| 3 | 3 | 7 | 7 |
| (9 rows) |
| |
| explain select * from nestloop_x as x, nestloop_y as y where y.j > x.i + x.j + 2; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..30030627.05 rows=47 width=16) |
| -> Nested Loop (cost=0.00..30030627.05 rows=16 width=16) |
| -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..30000001.00 rows=20 width=8) |
| -> Seq Scan on nestloop_x x (cost=0.00..30000000.20 rows=7 width=8) |
| -> Index Scan using nestloop_y_idx on nestloop_y y (cost=0.00..510.39 rows=1 width=8) |
| Index Cond: y.j > (x.i + x.j + 2) |
| Settings: enable_indexscan=on; enable_nestloop=on; optimizer=off; seq_page_cost=1e+07 |
| Optimizer status: Postgres query optimizer |
| (8 rows) |
| |
| select * from nestloop_x as x, nestloop_y as y where y.j > x.i + x.j + 2; |
| i | j | i | j |
| ---+---+---+--- |
| 1 | 1 | 5 | 5 |
| 1 | 1 | 6 | 6 |
| 1 | 1 | 7 | 7 |
| 2 | 2 | 7 | 7 |
| (4 rows) |
| |
| drop table nestloop_x, nestloop_y; |
| SET enable_seqscan = OFF; |
| SET enable_indexscan = ON; |
| DROP TABLE IF EXISTS bpchar_ops; |
| CREATE TABLE bpchar_ops(id INT8, v char(10)) DISTRIBUTED BY(id); |
| CREATE INDEX bpchar_ops_btree_idx ON bpchar_ops USING btree(v bpchar_pattern_ops); |
| INSERT INTO bpchar_ops VALUES (0, 'row'); |
| SELECT * FROM bpchar_ops WHERE v = 'row '::char(20); |
| id | v |
| ----+------------ |
| 0 | row |
| (1 row) |
| |
| DROP TABLE bpchar_ops; |
| -- |
| -- Test index rechecks with AO and AOCS tables (and heaps as well, for good measure) |
| -- |
| create table shape_heap (c circle) with (appendonly=false); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| create table shape_ao (c circle) with (appendonly=true, orientation=row); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| create table shape_aocs (c circle) with (appendonly=true, orientation=column); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| insert into shape_heap values ('<(0,0), 5>'); |
| insert into shape_ao values ('<(0,0), 5>'); |
| insert into shape_aocs values ('<(0,0), 5>'); |
| create index shape_heap_bb_idx on shape_heap using gist(c); |
| create index shape_ao_bb_idx on shape_ao using gist(c); |
| create index shape_aocs_bb_idx on shape_aocs using gist(c); |
| select c && '<(5,5), 1>'::circle, |
| c && '<(5,5), 2>'::circle, |
| c && '<(5,5), 3>'::circle |
| from shape_heap; |
| ?column? | ?column? | ?column? |
| ----------+----------+---------- |
| f | f | t |
| (1 row) |
| |
| -- Test the same values with (bitmap) index scans |
| -- |
| -- The first two values don't overlap with the value in the tables, <(0,0), 5>, |
| -- but their bounding boxes do. In a GiST index scan that uses the bounding |
| -- boxes, these will fetch the row from the index, but filtered out by the |
| -- recheck using the actual overlap operator. The third entry is sanity check |
| -- that the index returns any rows. |
| set enable_seqscan=off; |
| set enable_indexscan=off; |
| set enable_bitmapscan=on; |
| -- Use EXPLAIN to verify that these use a bitmap index scan |
| explain select * from shape_heap where c && '<(5,5), 1>'::circle; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=101.26..201.27 rows=1 width=24) |
| -> Bitmap Heap Scan on shape_heap (cost=101.26..201.27 rows=1 width=24) |
| Recheck Cond: c && '<(5,5),1>'::circle |
| -> Bitmap Index Scan on shape_heap_bb_idx (cost=0.00..101.26 rows=1 width=0) |
| Index Cond: c && '<(5,5),1>'::circle |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| explain select * from shape_ao where c && '<(5,5), 1>'::circle; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=101.26..201.27 rows=1 width=24) |
| -> Bitmap Heap Scan on shape_ao (cost=101.26..201.27 rows=1 width=24) |
| Recheck Cond: c && '<(5,5),1>'::circle |
| -> Bitmap Index Scan on shape_ao_bb_idx (cost=0.00..101.26 rows=1 width=0) |
| Index Cond: c && '<(5,5),1>'::circle |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| explain select * from shape_aocs where c && '<(5,5), 1>'::circle; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=101.26..201.27 rows=1 width=24) |
| -> Bitmap Heap Scan on shape_aocs (cost=101.26..201.27 rows=1 width=24) |
| Recheck Cond: c && '<(5,5),1>'::circle |
| -> Bitmap Index Scan on shape_aocs_bb_idx (cost=0.00..101.26 rows=1 width=0) |
| Index Cond: c && '<(5,5),1>'::circle |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- Test that they return correct results. |
| select * from shape_heap where c && '<(5,5), 1>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_ao where c && '<(5,5), 1>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_aocs where c && '<(5,5), 1>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_heap where c && '<(5,5), 2>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_ao where c && '<(5,5), 2>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_aocs where c && '<(5,5), 2>'::circle; |
| c |
| --- |
| (0 rows) |
| |
| select * from shape_heap where c && '<(5,5), 3>'::circle; |
| c |
| ----------- |
| <(0,0),5> |
| (1 row) |
| |
| select * from shape_ao where c && '<(5,5), 3>'::circle; |
| c |
| ----------- |
| <(0,0),5> |
| (1 row) |
| |
| select * from shape_aocs where c && '<(5,5), 3>'::circle; |
| c |
| ----------- |
| <(0,0),5> |
| (1 row) |
| |
| -- |
| -- Given a table with different column types |
| -- |
| CREATE TABLE table_with_reversed_index(a int, b bool, c text); |
| 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. |
| -- |
| -- And it has an index that is ordered differently than columns on the table. |
| -- |
| CREATE INDEX ON table_with_reversed_index(c, a); |
| INSERT INTO table_with_reversed_index VALUES (10, true, 'ab'); |
| -- |
| -- Then an index only scan should succeed. (i.e. varattno is set up correctly) |
| -- |
| SET enable_seqscan=off; |
| SET enable_bitmapscan=off; |
| SET optimizer_enable_tablescan=off; |
| SET optimizer_enable_indexscan=off; |
| SET optimizer_enable_indexonlyscan=on; |
| EXPLAIN SELECT c, a FROM table_with_reversed_index WHERE a > 5; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.12..10000000008.16 rows=1 width=7) |
| -> Index Only Scan using table_with_reversed_index_c_a_idx on table_with_reversed_index (cost=10000000000.12..10000000008.14 rows=1 width=7) |
| Index Cond: (a > 5) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| SELECT c, a FROM table_with_reversed_index WHERE a > 5; |
| c | a |
| ----+---- |
| ab | 10 |
| (1 row) |
| |
| RESET enable_seqscan; |
| RESET enable_bitmapscan; |
| RESET optimizer_enable_tablescan; |
| RESET optimizer_enable_indexscan; |
| RESET optimizer_enable_indexonlyscan; |
| -- |
| -- Test Hash indexes |
| -- |
| CREATE TABLE hash_tbl (a int, b int) DISTRIBUTED BY(a); |
| INSERT INTO hash_tbl select i,i FROM generate_series(1, 100)i; |
| ANALYZE hash_tbl; |
| CREATE INDEX hash_idx1 ON hash_tbl USING hash(b); |
| -- Now check the results by turning on indexscan |
| SET enable_seqscan = ON; |
| SET enable_indexscan = ON; |
| SET enable_bitmapscan = OFF; |
| SET optimizer_enable_tablescan =ON; |
| SET optimizer_enable_indexscan = ON; |
| SET optimizer_enable_bitmapscan = OFF; |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using hash_idx1 on hash_tbl |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3 and a=3; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using hash_idx1 on hash_tbl |
| Index Cond: (b = 3) |
| Filter: (a = 3) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3 and a=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3 or b=5; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_tbl |
| Filter: ((b = 3) OR (b = 5)) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3 or b=5; |
| a | b |
| ---+--- |
| 5 | 5 |
| 3 | 3 |
| (2 rows) |
| |
| -- Now check the results by turning on bitmapscan |
| SET enable_seqscan = OFF; |
| SET enable_indexscan = OFF; |
| SET enable_bitmapscan = ON; |
| SET optimizer_enable_tablescan =OFF; |
| SET optimizer_enable_indexscan = OFF; |
| SET optimizer_enable_bitmapscan = ON; |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl |
| Recheck Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx1 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3 and a=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl |
| Recheck Cond: (b = 3) |
| Filter: (a = 3) |
| -> Bitmap Index Scan on hash_idx1 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3 and a=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl WHERE b=3 or b=5; |
| QUERY PLAN |
| -------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl |
| Recheck Cond: ((b = 3) OR (b = 5)) |
| -> BitmapOr |
| -> Bitmap Index Scan on hash_idx1 |
| Index Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx1 |
| Index Cond: (b = 5) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| SELECT * FROM hash_tbl WHERE b=3 or b=5; |
| a | b |
| ---+--- |
| 3 | 3 |
| 5 | 5 |
| (2 rows) |
| |
| DROP INDEX hash_idx1; |
| DROP TABLE hash_tbl; |
| RESET enable_seqscan; |
| RESET enable_indexscan; |
| RESET enable_bitmapscan; |
| RESET optimizer_enable_tablescan; |
| RESET optimizer_enable_indexscan; |
| RESET optimizer_enable_bitmapscan; |
| -- Test Hash indexes with AO tables |
| CREATE TABLE hash_tbl_ao (a int, b int) WITH (appendonly = true) DISTRIBUTED BY(a); |
| INSERT INTO hash_tbl_ao select i,i FROM generate_series(1, 100)i; |
| ANALYZE hash_tbl_ao; |
| CREATE INDEX hash_idx2 ON hash_tbl_ao USING hash(b); |
| -- get results for comparison purposes |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| SELECT * FROM hash_tbl_ao WHERE b=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3 and a=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: (b = 3) |
| Filter: (a = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| SELECT * FROM hash_tbl_ao WHERE b=3 and a=3; |
| a | b |
| ---+--- |
| 3 | 3 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3 or b=5; |
| QUERY PLAN |
| -------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: ((b = 3) OR (b = 5)) |
| -> BitmapOr |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 5) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| SELECT * FROM hash_tbl_ao WHERE b=3 or b=5; |
| a | b |
| ---+--- |
| 3 | 3 |
| 5 | 5 |
| (2 rows) |
| |
| -- Now check the results by turning off seqscan/tablescan |
| SET enable_seqscan = OFF; |
| SET optimizer_enable_tablescan =OFF; |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3 and a=3; |
| QUERY PLAN |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: (b = 3) |
| Filter: (a = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_tbl_ao WHERE b=3 or b=5; |
| QUERY PLAN |
| -------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on hash_tbl_ao |
| Recheck Cond: ((b = 3) OR (b = 5)) |
| -> BitmapOr |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 3) |
| -> Bitmap Index Scan on hash_idx2 |
| Index Cond: (b = 5) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| DROP INDEX hash_idx2; |
| DROP TABLE hash_tbl_ao; |
| RESET enable_seqscan; |
| RESET optimizer_enable_tablescan; |
| -- Test hash indexes with partition table |
| CREATE TABLE hash_prt_tbl (a int, b int) DISTRIBUTED BY(a) PARTITION BY RANGE(a) |
| (PARTITION p1 START (1) END (500) INCLUSIVE, |
| PARTITION p2 START(501) END (1000) INCLUSIVE); |
| INSERT INTO hash_prt_tbl select i,i FROM generate_series(1, 1000)i; |
| ANALYZE hash_prt_tbl; |
| CREATE INDEX hash_idx3 ON hash_prt_tbl USING hash(b); |
| -- Now check the results by turning off dynamictablescan/seqscan |
| SET enable_seqscan = OFF; |
| SET optimizer_enable_dynamictablescan =OFF; |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_prt_tbl WHERE b=3; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 hash_prt_tbl_1 |
| Index Cond: (b = 3) |
| -> Index Scan using hash_prt_tbl_1_prt_p2_b_idx on hash_prt_tbl_1_prt_p2 hash_prt_tbl_2 |
| Index Cond: (b = 3) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_prt_tbl WHERE b=3 and a=3; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 hash_prt_tbl |
| Index Cond: (b = 3) |
| Filter: (a = 3) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM hash_prt_tbl WHERE b=3 or b=5; |
| QUERY PLAN |
| -------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p1 hash_prt_tbl_1 |
| Recheck Cond: ((b = 3) OR (b = 5)) |
| -> BitmapOr |
| -> Bitmap Index Scan on hash_prt_tbl_1_prt_p1_b_idx |
| Index Cond: (b = 3) |
| -> Bitmap Index Scan on hash_prt_tbl_1_prt_p1_b_idx |
| Index Cond: (b = 5) |
| -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p2 hash_prt_tbl_2 |
| Recheck Cond: ((b = 3) OR (b = 5)) |
| -> BitmapOr |
| -> Bitmap Index Scan on hash_prt_tbl_1_prt_p2_b_idx |
| Index Cond: (b = 3) |
| -> Bitmap Index Scan on hash_prt_tbl_1_prt_p2_b_idx |
| Index Cond: (b = 5) |
| Optimizer: Postgres query optimizer |
| (17 rows) |
| |
| DROP INDEX hash_idx3; |
| DROP TABLE hash_prt_tbl; |
| RESET enable_seqscan; |
| RESET optimizer_enable_dynamictablescan; |
| -- |
| -- Test ORCA generates Bitmap/IndexScan alternative for ScalarArrayOpExpr ANY only |
| -- |
| CREATE TABLE bitmap_alt (id int, bitmap_idx_col int, btree_idx_col int, hash_idx_col int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 bitmap_alt_idx1 on bitmap_alt using bitmap(bitmap_idx_col); |
| CREATE INDEX bitmap_alt_idx2 on bitmap_alt using btree(btree_idx_col); |
| CREATE INDEX bitmap_alt_idx3 on bitmap_alt using hash(hash_idx_col); |
| INSERT INTO bitmap_alt SELECT i, i, i, i from generate_series(1,10)i; |
| ANALYZE bitmap_alt; |
| -- ORCA should generate bitmap index scan plans for the following |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE bitmap_idx_col IN (3, 5); |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on bitmap_alt |
| Recheck Cond: (bitmap_idx_col = ANY ('{3,5}'::integer[])) |
| -> Bitmap Index Scan on bitmap_alt_idx1 |
| Index Cond: (bitmap_idx_col = ANY ('{3,5}'::integer[])) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| SELECT * FROM bitmap_alt WHERE bitmap_idx_col IN (3, 5); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| 3 | 3 | 3 | 3 |
| 5 | 5 | 5 | 5 |
| (2 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE btree_idx_col IN (3, 5); |
| QUERY PLAN |
| ---------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using bitmap_alt_idx2 on bitmap_alt |
| Index Cond: (btree_idx_col = ANY ('{3,5}'::integer[])) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| SELECT * FROM bitmap_alt WHERE btree_idx_col IN (3, 5); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| 5 | 5 | 5 | 5 |
| 3 | 3 | 3 | 3 |
| (2 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE hash_idx_col IN (3, 5); |
| QUERY PLAN |
| --------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on bitmap_alt |
| Recheck Cond: (hash_idx_col = ANY ('{3,5}'::integer[])) |
| -> Bitmap Index Scan on bitmap_alt_idx3 |
| Index Cond: (hash_idx_col = ANY ('{3,5}'::integer[])) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| SELECT * FROM bitmap_alt WHERE hash_idx_col IN (3, 5); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| 3 | 3 | 3 | 3 |
| 5 | 5 | 5 | 5 |
| (2 rows) |
| |
| -- ORCA should generate seq scan plans for the following |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE bitmap_idx_col=ALL(ARRAY[3, 5]); |
| QUERY PLAN |
| ------------------------------------- |
| Result |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| SELECT * FROM bitmap_alt WHERE bitmap_idx_col=ALL(ARRAY[3, 5]); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| (0 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE btree_idx_col=ALL(ARRAY[3, 5]); |
| QUERY PLAN |
| ------------------------------------- |
| Result |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| SELECT * FROM bitmap_alt WHERE btree_idx_col=ALL(ARRAY[3, 5]); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| (0 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM bitmap_alt WHERE hash_idx_col=ALL(ARRAY[3, 5]); |
| QUERY PLAN |
| ------------------------------------- |
| Result |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| SELECT * FROM bitmap_alt WHERE hash_idx_col=ALL(ARRAY[3, 5]); |
| id | bitmap_idx_col | btree_idx_col | hash_idx_col |
| ----+----------------+---------------+-------------- |
| (0 rows) |
| |
| -- |
| -- Test ORCA considers ScalarArrayOp in indexqual for partitioned table |
| -- with multikey indexes only when predicate key is the first index key |
| -- (similar test for non-partitioned tables in create_index) |
| -- |
| CREATE TABLE pt_with_multikey_index (a int, key1 char(6), key2 char(1)) |
| PARTITION BY list(key2) |
| (PARTITION p1 VALUES ('R'), PARTITION p2 VALUES ('G'), DEFAULT PARTITION other); |
| 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 multikey_idx on pt_with_multikey_index (key1, key2); |
| INSERT INTO pt_with_multikey_index SELECT i, 'KEY'||i, 'R' from generate_series(1,500)i; |
| INSERT INTO pt_with_multikey_index SELECT i, 'KEY'||i, 'G' from generate_series(1,500)i; |
| INSERT INTO pt_with_multikey_index SELECT i, 'KEY'||i, 'B' from generate_series(1,500)i; |
| explain (costs off) |
| SELECT key1 FROM pt_with_multikey_index |
| WHERE key1 IN ('KEY55', 'KEY65', 'KEY5') |
| ORDER BY key1; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: pt_with_multikey_index.key1 |
| -> Merge Append |
| Sort Key: pt_with_multikey_index.key1 |
| -> Index Only Scan using pt_with_multikey_index_1_prt_p2_key1_key2_idx on pt_with_multikey_index_1_prt_p2 pt_with_multikey_index_1 |
| Index Cond: (key1 = ANY ('{KEY55,KEY65,KEY5}'::bpchar[])) |
| -> Index Only Scan using pt_with_multikey_index_1_prt_p1_key1_key2_idx on pt_with_multikey_index_1_prt_p1 pt_with_multikey_index_2 |
| Index Cond: (key1 = ANY ('{KEY55,KEY65,KEY5}'::bpchar[])) |
| -> Index Only Scan using pt_with_multikey_index_1_prt_other_key1_key2_idx on pt_with_multikey_index_1_prt_other pt_with_multikey_index_3 |
| Index Cond: (key1 = ANY ('{KEY55,KEY65,KEY5}'::bpchar[])) |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| SELECT key1 FROM pt_with_multikey_index |
| WHERE key1 IN ('KEY55', 'KEY65', 'KEY5') |
| ORDER BY key1; |
| key1 |
| -------- |
| KEY5 |
| KEY5 |
| KEY5 |
| KEY55 |
| KEY55 |
| KEY55 |
| KEY65 |
| KEY65 |
| KEY65 |
| (9 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM pt_with_multikey_index |
| WHERE key1 = 'KEY55' AND key2 IN ('R', 'G') |
| ORDER BY key2; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: pt_with_multikey_index.key2 |
| -> Sort |
| Sort Key: pt_with_multikey_index.key2 |
| -> Append |
| -> Index Scan using pt_with_multikey_index_1_prt_p2_key1_key2_idx on pt_with_multikey_index_1_prt_p2 pt_with_multikey_index_1 |
| Index Cond: ((key1 = 'KEY55'::bpchar) AND (key2 = ANY ('{R,G}'::bpchar[]))) |
| -> Index Scan using pt_with_multikey_index_1_prt_p1_key1_key2_idx on pt_with_multikey_index_1_prt_p1 pt_with_multikey_index_2 |
| Index Cond: ((key1 = 'KEY55'::bpchar) AND (key2 = ANY ('{R,G}'::bpchar[]))) |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| SELECT * FROM pt_with_multikey_index |
| WHERE key1 = 'KEY55' AND key2 IN ('R', 'G') |
| ORDER BY key2; |
| a | key1 | key2 |
| ----+--------+------ |
| 55 | KEY55 | G |
| 55 | KEY55 | R |
| (2 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM pt_with_multikey_index |
| WHERE key1 IN ('KEY55', 'KEY65') AND key2 = 'R' |
| ORDER BY key1; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: pt_with_multikey_index.key1 |
| -> Index Scan using pt_with_multikey_index_1_prt_p1_key1_key2_idx on pt_with_multikey_index_1_prt_p1 pt_with_multikey_index |
| Index Cond: ((key1 = ANY ('{KEY55,KEY65}'::bpchar[])) AND (key2 = 'R'::bpchar)) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| SELECT * FROM pt_with_multikey_index |
| WHERE key1 IN ('KEY55', 'KEY65') AND key2 = 'R' |
| ORDER BY key1; |
| a | key1 | key2 |
| ----+--------+------ |
| 55 | KEY55 | R |
| 65 | KEY65 | R |
| (2 rows) |
| |
| -- |
| -- Enable the index only scan in append only table. |
| -- Note: expect ORCA to use seq scan rather than index only scan like planner, |
| -- because ORCA hasn't yet implemented index only scan for AO/CO tables. |
| -- |
| CREATE TABLE bfv_index_only_ao(a int, b int) WITH (appendonly =true); |
| CREATE INDEX bfv_index_only_ao_a_b on bfv_index_only_ao(a) include (b); |
| insert into bfv_index_only_ao select i,i from generate_series(1, 10000) i; |
| explain select count(*) from bfv_index_only_ao where a < 100; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=340000382.72..340000382.73 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=340000382.67..340000382.72 rows=3 width=8) |
| -> Partial Aggregate (cost=340000382.67..340000382.68 rows=1 width=8) |
| -> Seq Scan on bfv_index_only_ao (cost=0.00..340000358.75 rows=9567 width=0) |
| Filter: (a < 100) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| select count(*) from bfv_index_only_ao where a < 100; |
| count |
| ------- |
| 99 |
| (1 row) |
| |
| explain select count(*) from bfv_index_only_ao where a < 1000; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=340000382.72..340000382.73 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=340000382.67..340000382.72 rows=3 width=8) |
| -> Partial Aggregate (cost=340000382.67..340000382.68 rows=1 width=8) |
| -> Seq Scan on bfv_index_only_ao (cost=0.00..340000358.75 rows=9567 width=0) |
| Filter: (a < 1000) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| select count(*) from bfv_index_only_ao where a < 1000; |
| count |
| ------- |
| 999 |
| (1 row) |
| |
| CREATE TABLE bfv_index_only_aocs(a int, b int) WITH (appendonly =true, orientation=column); |
| CREATE INDEX bfv_index_only_aocs_a_b on bfv_index_only_aocs(a) include (b); |
| insert into bfv_index_only_aocs select i,i from generate_series(1, 10000) i; |
| explain select count(*) from bfv_index_only_aocs where a < 100; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=340000382.72..340000382.73 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=340000382.67..340000382.72 rows=3 width=8) |
| -> Partial Aggregate (cost=340000382.67..340000382.68 rows=1 width=8) |
| -> Seq Scan on bfv_index_only_aocs (cost=0.00..340000358.75 rows=9567 width=0) |
| Filter: (a < 100) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| select count(*) from bfv_index_only_aocs where a < 100; |
| count |
| ------- |
| 99 |
| (1 row) |
| |
| explain select count(*) from bfv_index_only_aocs where a < 1000; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=340000382.72..340000382.73 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=340000382.67..340000382.72 rows=3 width=8) |
| -> Partial Aggregate (cost=340000382.67..340000382.68 rows=1 width=8) |
| -> Seq Scan on bfv_index_only_aocs (cost=0.00..340000358.75 rows=9567 width=0) |
| Filter: (a < 1000) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| select count(*) from bfv_index_only_aocs where a < 1000; |
| count |
| ------- |
| 999 |
| (1 row) |
| |
| -- The following tests are to verify a fix that allows ORCA to |
| -- choose the bitmap index scan alternative when the predicate |
| -- is in the form of `value operator cast(column)`. The fix |
| -- converts the scalar comparison expression to the more common |
| -- form of `cast(column) operator value` in the preprocessor. |
| -- Each test includes two queries. One query's predicate has |
| -- the column on the left side, and the other has the column |
| -- on the right side. We expect the two queries to generate |
| -- identical plans with bitmap index scan. |
| -- Index only scan will probably be selected once index only |
| -- scan in enabled for AO tables in ORCA. To prevent retain |
| -- the bitmap scan alternative, turn off index only scan. |
| set optimizer_enable_indexonlyscan=off; |
| -- Test AO table |
| -- Index scan is disabled in AO table, so bitmap scan is the |
| -- most performant |
| create table ao_tbl ( |
| path_hash character varying(10) |
| ) with (appendonly='true'); |
| create index ao_idx on ao_tbl using btree (path_hash); |
| insert into ao_tbl select 'abc' from generate_series(1,20) i; |
| analyze ao_tbl; |
| -- identical plans |
| explain select * from ao_tbl where path_hash = 'ABC'; |
| QUERY PLAN |
| ---------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) (cost=4.15..8.18 rows=1 width=4) |
| -> Bitmap Heap Scan on ao_tbl (cost=4.15..8.16 rows=1 width=4) |
| Recheck Cond: ((path_hash)::text = 'ABC'::text) |
| -> Bitmap Index Scan on ao_idx (cost=0.00..4.15 rows=1 width=0) |
| Index Cond: ((path_hash)::text = 'ABC'::text) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| explain select * from ao_tbl where 'ABC' = path_hash; |
| QUERY PLAN |
| ---------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) (cost=4.15..8.18 rows=1 width=4) |
| -> Bitmap Heap Scan on ao_tbl (cost=4.15..8.16 rows=1 width=4) |
| Recheck Cond: ('ABC'::text = (path_hash)::text) |
| -> Bitmap Index Scan on ao_idx (cost=0.00..4.15 rows=1 width=0) |
| Index Cond: ((path_hash)::text = 'ABC'::text) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- Test AO partition table |
| -- Dynamic index scan is disabled in AO table, so dynamic bitmap |
| -- scan is the most performant |
| create table part_tbl ( |
| path_hash character varying(10) |
| ) partition by list(path_hash) |
| (partition pics values('a') , |
| default partition other with (appendonly='true')); |
| create index part_idx on part_tbl using btree (path_hash); |
| insert into part_tbl select 'abc' from generate_series(1,20) i; |
| analyze part_tbl; |
| -- identical plans |
| explain select * from part_tbl where path_hash = 'ABC'; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) (cost=4.15..8.18 rows=1 width=4) |
| -> Bitmap Heap Scan on part_tbl_1_prt_other part_tbl (cost=4.15..8.16 rows=1 width=4) |
| Recheck Cond: ((path_hash)::text = 'ABC'::text) |
| -> Bitmap Index Scan on part_tbl_1_prt_other_path_hash_idx (cost=0.00..4.15 rows=1 width=0) |
| Index Cond: ((path_hash)::text = 'ABC'::text) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| explain select * from part_tbl where 'ABC' = path_hash; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) (cost=4.15..8.18 rows=1 width=4) |
| -> Bitmap Heap Scan on part_tbl_1_prt_other part_tbl (cost=4.15..8.16 rows=1 width=4) |
| Recheck Cond: ('ABC'::text = (path_hash)::text) |
| -> Bitmap Index Scan on part_tbl_1_prt_other_path_hash_idx (cost=0.00..4.15 rows=1 width=0) |
| Index Cond: ((path_hash)::text = 'ABC'::text) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- Test table indexed on two columns |
| -- Two indices allow ORCA to generate the bitmap scan alternative |
| create table two_idx_tbl (x varchar(10), y varchar(10)); |
| create index x_idx on two_idx_tbl using btree (x); |
| create index y_idx on two_idx_tbl using btree (y); |
| insert into two_idx_tbl select 'aa', 'bb' from generate_series(1,10000) i; |
| analyze two_idx_tbl; |
| -- encourage bitmap scan by discouraging index scan |
| set optimizer_enable_indexscan=off; |
| -- identical plans |
| explain select * from two_idx_tbl where x = 'cc' or y = 'dd'; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=8.34..12.37 rows=1 width=6) |
| -> Bitmap Heap Scan on two_idx_tbl (cost=8.34..12.35 rows=1 width=6) |
| Recheck Cond: (((x)::text = 'cc'::text) OR ((y)::text = 'dd'::text)) |
| -> BitmapOr (cost=8.34..8.34 rows=1 width=0) |
| -> Bitmap Index Scan on x_idx (cost=0.00..4.17 rows=1 width=0) |
| Index Cond: ((x)::text = 'cc'::text) |
| -> Bitmap Index Scan on y_idx (cost=0.00..4.17 rows=1 width=0) |
| Index Cond: ((y)::text = 'dd'::text) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| explain select * from two_idx_tbl where 'cc' = x or 'dd' = y; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=8.34..12.37 rows=1 width=6) |
| -> Bitmap Heap Scan on two_idx_tbl (cost=8.34..12.35 rows=1 width=6) |
| Recheck Cond: (('cc'::text = (x)::text) OR ('dd'::text = (y)::text)) |
| -> BitmapOr (cost=8.34..8.34 rows=1 width=0) |
| -> Bitmap Index Scan on x_idx (cost=0.00..4.17 rows=1 width=0) |
| Index Cond: ((x)::text = 'cc'::text) |
| -> Bitmap Index Scan on y_idx (cost=0.00..4.17 rows=1 width=0) |
| Index Cond: ((y)::text = 'dd'::text) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| RESET optimizer_enable_indexscan; |
| RESET optimizer_enable_indexonlyscan; |
| RESET enable_indexonlyscan; |
| RESET seq_page_cost; |
| -- Test IndexNLJoin on IndexOnlyScan in ORCA (both heap and AOCS table) |
| create table index_only_join_test (a int, b int) distributed by (a); |
| create table index_only_join_test_aocs (a int, b int) with (appendonly='true') distributed by (a); |
| create index index_only_join_test_a_idx on index_only_join_test(a); |
| create index index_only_join_test_b_idx on index_only_join_test(b) include (a); |
| create index index_only_join_test_aocs_a_idx on index_only_join_test_aocs(a); |
| create index index_only_join_test_aocs_b_idx on index_only_join_test_aocs(b) include (a); |
| insert into index_only_join_test select i,i from generate_series(1, 100)i; |
| insert into index_only_join_test_aocs select i,i from generate_series(1, 100)i; |
| analyze index_only_join_test; |
| analyze index_only_join_test_aocs; |
| set enable_nestloop to on; |
| set enable_seqscan to off; |
| set optimizer_enable_indexscan to off; |
| explain select t1.a from index_only_join_test t1, index_only_join_test t2 where t1.a = t2.a and t1.b < 10; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..21.14 rows=13 width=4) |
| -> Nested Loop (cost=0.31..20.96 rows=4 width=4) |
| -> Index Only Scan using index_only_join_test_b_idx on index_only_join_test t1 (cost=0.15..8.41 rows=3 width=4) |
| Index Cond: (b < 10) |
| -> Index Only Scan using index_only_join_test_a_idx on index_only_join_test t2 (cost=0.15..4.17 rows=1 width=4) |
| Index Cond: (a = t1.a) |
| Optimizer: Postgres-based planner |
| (7 rows) |
| |
| reset optimizer_enable_indexscan; |
| explain select t1.a from index_only_join_test_aocs t1, index_only_join_test_aocs t2 where t1.a = t2.a and t1.b < 10; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=6.83..26.27 rows=8 width=4) |
| -> Nested Loop (cost=6.83..26.16 rows=3 width=4) |
| -> Bitmap Heap Scan on index_only_join_test_aocs t1 (cost=4.20..8.24 rows=3 width=4) |
| Recheck Cond: (b < 10) |
| -> Bitmap Index Scan on index_only_join_test_aocs_b_idx (cost=0.00..4.20 rows=3 width=0) |
| Index Cond: (b < 10) |
| -> Bitmap Heap Scan on index_only_join_test_aocs t2 (cost=2.62..6.63 rows=1 width=4) |
| Recheck Cond: (a = t1.a) |
| -> Bitmap Index Scan on index_only_join_test_aocs_a_idx (cost=0.00..2.62 rows=1 width=0) |
| Index Cond: (a = t1.a) |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| reset enable_nestloop; |
| reset enable_seqscan; |
| drop table index_only_join_test; |
| drop table index_only_join_test_aocs; |
