表达式索引

• 复制
  复制成功

postgres=# create table t_upper(id int,mc text);
postgres=# create index t_upper_mc on t_upper(mc);       
postgres=# insert into t_upper select t,md5(t::text) from generate_series(1,10000) as t; 
postgres=# analyze t_upper;
ANALYZE

postgres=# explain select * from t_upper where upper(mc)=md5('1');
                               QUERY PLAN                               
------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Seq Scan on t_upper  (cost=0.00..135.58 rows=25 width=37)
         Filter: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(4 rows)

postgres=# create index t_upper_mc on t_upper(upper(mc));       
CREATE INDEX
postgres=# explain select * from t_upper where upper(mc)=md5('1');
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Bitmap Heap Scan on t_upper  (cost=2.48..32.43 rows=25 width=37)
         Recheck Cond: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
         ->  Bitmap Index Scan on t_upper_mc  (cost=0.00..2.47 rows=25 width=0)
               Index Cond: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(6 rows)