iihero@ChinaUnix, ehero.[iihero] 数据库技术的痴迷爱好者. 您可以通过iihero AT qq.com联系到我 以下是我的三本图书: Sybase ASE in Action, Oracle Spatial及OCI高级编程, Java2网络协议内幕
分类： Mysql/postgreSQL
2013-07-24 11:12:17
这里只是做一个简单的实验，似乎不能完全证明position()函数比like查询要快。
SELECT generate_series(1,10) as id, substr('abcdefghijklmnopqrstuvwxyz',1, (random()*26)::integer) as col2 into t1;
explain analyze select count(*) from t1 where col2 like '%bc%'
--------------------------------------------------------------------------------
----------------------------------
Aggregate (cost=20821.67..20821.68 rows=1 width=0) (actual time=407.894..407.8
94 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..18563.00 rows=903467 width=0) (actual time=0.
105..320.662 rows=903565 loops=1)
Filter: (col2 ~~ '%bc%'::text)
Rows Removed by Filter: 96435
Total runtime: 408.081 ms
(5 rows)
iihero=# explain analyze select count(*) from t1 where position('bc' in col2)>0;
--------------------------------------------------------------------------------
----------------------------------
Aggregate (cost=21896.33..21896.34 rows=1 width=0) (actual time=714.827..714.8
27 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..21063.00 rows=333333 width=0) (actual time=0.
140..629.252 rows=903565 loops=1)
Filter: ("position"(col2, 'bc'::text) > 0)
Rows Removed by Filter: 96435
Total runtime: 714.944 ms
(5 rows)
iihero=# create index idx_pos_bc_t1 on t1(position('bc' in col2));
--------------------------------------------------------------------------------
-------------------------------------------------------------
Aggregate (cost=18141.01..18141.02 rows=1 width=0) (actual time=388.336..388.3
36 rows=1 loops=1)
-> Bitmap Heap Scan on t1 (cost=6244.68..17307.68 rows=333333 width=0) (act
ual time=197.816..310.730 rows=903565 loops=1)
Recheck Cond: ("position"(col2, 'bc'::text) > 0)
-> Bitmap Index Scan on idx_pos_bc_t1 (cost=0.00..6161.35 rows=333333
width=0) (actual time=196.172..196.172 rows=903565 loops=1)
Index Cond: ("position"(col2, 'bc'::text) > 0)
Total runtime: 388.639 ms
(6 rows)
只有要函数索引完全能用上的情况下，才算有效。 当position位于左侧表达式时，并且含有动态变量，索引就无法用上了。
