bind_values

用在查询语句中，控制Values表（IN (expression_list)优化而来）的行为，使其不参与连接顺序交换且尽可能的靠近相关联的表。 这里的bind指的是Values表与关联表之间的绑定。

举例:

--  清理环境
drop table t1 cascade;
drop table t2 cascade;

--  创建表
create table t1(a int, b int);
create table t2(a int, b int);
insert into t1 select generate_series(1, 10)， generate_series(1, 10);
insert into t2 select generate_series(1, 1000)， generate_series(1, 1000);
analyze t1;
analyze t2;

-- 确保in可以成为可以提升的values
set ENABLE_EXPAND_AEXPR_IN=FALSE;
set FORCE_EXPAND_AEXPR_IN_NUM = 0;
set ENABLE_SCALARARRAYOPEXPR_TO_VALUES=TRUE;


-- 通过hint强制将values表和关联表t1进行绑定
explain select /*+ bind_values */ * from t1, t2 where t1.a = t2.a 
and t2.b in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
               11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
			   21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
			   31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
			   41, 42, 43, 44, 45, 46, 47, 48, 49, 50);
QUERY PLAN(text)      
----------------------
Hash Join(Inner Join)  (cost=8.47..30.92 rows=1 width=16)
  Hash Cond: ("outer".A = "inner".A)
  ->  Hash Join(Left Semi Join)  (cost=1.25..23.50 rows=50 width=8)
        Hash Cond: ("outer".B = "inner"."column1")
        ->  Seq Scan on T2  (cost=0.00..18.00 rows=1000 width=8)
        ->  Hash  (cost=0.63..0.63 rows=50 width=4)
              ->  Values Scan on *VALUES*  (cost=0.00..0.63 rows=50 width=4)
  ->  Hash  (cost=7.10..7.10 rows=10 width=8)
        ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 width=8)
总数目：9


-- 不使用bind_values hint			   
explain select * from t1, t2 where t1.a = t2.a 
and t2.b in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
               11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
			   21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
			   31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
			   41, 42, 43, 44, 45, 46, 47, 48, 49, 50);
QUERY PLAN(text)      
----------------------
Hash Join(Inner Join)  (cost=29.95..30.15 rows=1 width=16)
  Hash Cond: ("outer"."column1" = "inner".B)
  ->  HashAggregate  (cost=0.75..0.75 rows=50 width=4)
        Group Key: "column1"
        ->  Values Scan on *VALUES*  (cost=0.00..0.63 rows=50 width=4)
  ->  Hash  (cost=29.08..29.08 rows=10 width=16)
        ->  Hash Join(Inner Join)  (cost=7.22..29.08 rows=10 width=16)
              Hash Cond: ("outer".A = "inner".A)
              ->  Seq Scan on T2  (cost=0.00..18.00 rows=1000 width=8)
              ->  Hash  (cost=7.10..7.10 rows=10 width=8)
                    ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 width=8)
总数目：11
		
--  清理环境
reset all;
drop table t1 cascade;
drop table t2 cascade;