表達式索引
更新時間 2025-02-14 10:21:44
最近更新時間: 2025-02-14 10:21:44
分享文章
本文為您介紹如何管理表達式索引。
teledb=# create table t_upper(id int,mc text);
CREATE TABLE
teledb=# create index t_upper_mc on t_upper(mc);
CREATE INDEX
teledb=# insert into t_upper select t,md5(t::text) from generate_series(1,10000) as t;
INSERT 0 10000
teledb=# analyze t_upper;
ANALYZE
teledb=# 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: dn01, dn02
-> Seq Scan on t_upper (cost=0.00..133.58 rows=25 width=37)
Filter: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(4 rows)
teledb=# create index t_upper_mc on t_upper(upper(mc));
ERROR: relation "t_upper_mc" already exists
teledb=# create index t_upper_mc1 on t_upper(upper(mc));
CREATE INDEX
teledb=# 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: dn01, dn02
-> Bitmap Heap Scan on t_upper (cost=4.48..50.94 rows=25 width=37)
Recheck Cond: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
-> Bitmap Index Scan on t_upper_mc1 (cost=0.00..4.47 rows=25 width=0)
Index Cond: (upper(mc) = 'c4ca4238a0b923820dcc509a6f75849b'::text)
(6 rows)
