Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1024014
  • 博文数量: 185
  • 博客积分: 50
  • 博客等级: 民兵
  • 技术积分: 3934
  • 用 户 组: 普通用户
  • 注册时间: 2007-09-11 13:11
个人简介

iihero@ChinaUnix, ehero.[iihero] 数据库技术的痴迷爱好者. 您可以通过iihero AT qq.com联系到我 以下是我的三本图书: Sybase ASE in Action, Oracle Spatial及OCI高级编程, Java2网络协议内幕

文章分类

全部博文(185)

文章存档

2014年(4)

2013年(181)

分类: 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位于左侧表达式时,并且含有动态变量,索引就无法用上了。


阅读(1092) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~