bfv_index.out

-- 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);
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;
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)

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
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 Greenplum Database 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.
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_dflt" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_2" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_3" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_4" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_5" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_6" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_7" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_8" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_9" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_10" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_11" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_12" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_13" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_14" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_15" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_16" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_17" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_18" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_19" for table "bfv_tab2_facttable1"
NOTICE:  CREATE TABLE will create partition "bfv_tab2_facttable1_1_prt_20" for table "bfv_tab2_facttable1"
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 Greenplum Database 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 Greenplum Database 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); 
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_dflt"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_2"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_3"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_4"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_5"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_6"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_7"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_8"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_9"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_10"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_11"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_12"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_13"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_14"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_15"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_16"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_17"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_18"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_19"
NOTICE:  building index for child partition "bfv_tab2_facttable1_1_prt_20"
--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=27.51..27.52 rows=1 width=8)
   ->  Gather Motion 3:1  (slice3; segments: 3)  (cost=27.44..27.49 rows=1 width=8)
         ->  Aggregate  (cost=27.44..27.45 rows=1 width=8)
               ->  Hash Join  (cost=6.84..27.43 rows=2 width=0)
                     Hash Cond: ft.wk_id = dt.wk_id
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=3.61..24.14 rows=3 width=2)
                           Hash Key: ft.wk_id
                           ->  Hash Join  (cost=3.61..23.99 rows=3 width=2)
                                 Hash Cond: ft.id = dt1.id
                                 ->  Append  (cost=0.00..20.20 rows=7 width=6)
                                       ->  Seq Scan on bfv_tab2_facttable1_1_prt_dflt ft  (cost=0.00..1.00 rows=1 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..2.02 rows=1 width=6)
                                 ->  Hash  (cost=3.35..3.35 rows=7 width=4)
                                       ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..3.35 rows=7 width=4)
                                             ->  Seq Scan on bfv_tab2_dimtabl1 dt1  (cost=0.00..3.07 rows=3 width=4)
                     ->  Hash  (cost=3.10..3.10 rows=4 width=2)
                           ->  Seq Scan on bfv_tab2_dimdate dt  (cost=0.00..3.10 rows=4 width=2)
 Optimizer status: Postgres query optimizer
(36 rows)

-- start_ignore
create language plpythonu;
ERROR:  language "plpythonu" 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 plpythonu;
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 Greenplum Database 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(1000) inclusive);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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.
NOTICE:  CREATE TABLE will create partition "tbl23383_partitioned_1_prt_p1" for table "tbl23383_partitioned"
NOTICE:  CREATE TABLE will create partition "tbl23383_partitioned_1_prt_p2" for table "tbl23383_partitioned"
insert into Tbl23383_partitioned select g,g,g,g from generate_series(1,1000) g;
create index idx23383_b on Tbl23383_partitioned(b);
NOTICE:  building index for child partition "tbl23383_partitioned_1_prt_p1"
NOTICE:  building index for child partition "tbl23383_partitioned_1_prt_p2"
-- 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 Greenplum Database 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)