-- 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: legacy query optimizer (7 rows) --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) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_dflt ft (cost=0.00..1.00 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_2 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_3 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_4 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_5 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_6 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_7 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_8 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_9 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_10 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_11 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_12 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_13 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_14 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_15 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_16 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_17 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_18 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_19 ft (cost=0.00..1.01 rows=1 width=6) -> Append-only Columnar Scan on bfv_tab2_facttable1_1_prt_20 ft (cost=0.00..1.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: legacy 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); -- start_ignore select disable_xform('CXformDynamicGet2DynamicTableScan'); disable_xform ----------------------------------------------- CXformDynamicGet2DynamicTableScan is disabled (1 row) -- end_ignore 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; -- when optimizer is on PG exception raised during DXL->PlStmt translation for IndexScan query -- 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: legacy query optimizer (4 rows) drop table tbl_ab; drop function count_index_scans(text);