给PSQL表row中字段建立索引,并证明建立成功

背景描述:
组内项目有一张表叫做Session,记录的是每一条测试的详细信息,包括id,创建者,创建时间,测试时长,测试状态(FINISH,TESTING,NO_FRAME等),还有Properties(是一个大json,也就是什么字段都可以往里面塞,例如项目包名,软件版本号,测试各项配置)等。
现在需要加一个功能,这个功能是在admin页面上,也就是后台管理页面上做的。当你在页面上hover某条测试的包名(例如:com.hhh.goodgame)的时候,需要显示该测试包,在我们项目内完成了多少次测试。

最开始的做法就是正常的调用ListSession函数,并传入参数进去,获得api返回的count数。

http.listSessions({equalPackageName:searchedSession.Properties.packageName,testStatus:'FINISHED', withDelete: true})

但是,由于Session列表的数目过于大,有15w条数据,所以每次查询速度都很慢。于是,我们决定在Properties.packageName字段上面,建立一个索引。最开始的时候,我还在纠结能不能给字段的字段建立索引,后来查了一下,PSQL真的很强大,它可以支持:表达式索引。

create index idx_session_properties_packagename on upa.profilingsession ((properties ->> 'packageName'));

建立好索引之后,就是验证它是否生效了。在将我的liquibase修改推向master分支之前,我在本地的数据库里进行了模拟。

点开表的详情已经可以看得到了。然后,决定再用explain试一下,看看index是否有被好好使用,但是结果如下图所示。

然后,我就很奇怪,为什么明明表详情内已经可以看到index idx_session_properties_packagename,但是搜索的时候却没有用上。于是猜想,是不是因为我本地表内数据只有7行,数据量太小了?于是我怒插20行,最终得到了如下图所示的结果。

总结学习:
1)新增的 index 並沒有在 query plain 裡面。為什麼postgres不選擇index scan卻選擇seq scan?
在數據量很小的時候, seq scan 會比 index scan 更加有效。
pgsql 的 query plan 有一個評估值的方式來 “預測” 哪一種方式比較快。index scan 至少要發生兩次 I/O,一次是讀取索引塊,一次是讀取數據塊,代價遠高於 seq can。當 index 很大的時候,情況可能會更加複雜。

2)postgresql中的各种scan的比较

  • Seq Scan(就是按照表的记录的排列顺序从头到尾依次检索扫描,每次扫描要取到所有的记录。这也是最简单最基础的扫表方式,扫描的代价比较大)
  • Index Scan(对于给定的查询,我们先扫描一遍索引,从索引中找到符合要求的记录的位置(指针),再定位到表中具体的Page去取。等于是两次I/O,先走索引,再走取表记录,不同于全表扫描的是只取所需数据对应的Page,I/O量较小)
  • Bitmap Heap Scan(当 PostgreSQL 需要合并索引访问的结果子集时 会用到这种方式 ,通常情况是在用到 "or",“and”时会出现"Bitmap heap scan")
  • index only scan(建立 index时,所包含的字段集合,囊括了我们查询语句中的字段,这样,提取出相应的index ,就不必再次提取数据块了)
  • 当获取的数据分布很大(比如70%以上)时,用index scan 已经没有意义了,因为数据太多了,走索引再走表的代价已经超过了单纯走表的代价了。就不如用全表扫描了。
  • 而数据分布较小(比如 1.7%),则索引的优势就体现出来了。可能bitmap index scan的性能就更好(相比于index scan,因为它减少了index的重复扫描)。
  • 当数据更少的时候,用index scan可能就更好(索引重复的可能性较小且回避了在内存中排序的代价)。
作者:灰灰原文地址:https://segmentfault.com/a/1190000043150693

%s 个评论

要回复文章请先登录注册