Postgre SQL从哪个点看要优于Oracle SQL Server MySQL

文章转载自公众号AustinDatabases

很早的一篇文字, 今天遇到了问题,开发问我怎么解决, 又翻出来,PG的优越性比Oracle  SQL Server MySQL高明的地方,就体现在下方的文字:

本来上次是写过这个Postgre SQL的功能的，但上次在一个论坛里面发现其实大家对这个功能认识上是有误区的，所示这次是的详细的一次文字。

功能很简单的就是模糊查询，类似select * from table where column1 like‘%PG牛逼%’；然后走一个靠谱的索引的查询，Oracle打死他都不行，当然可以走全文索引，那MySQL, SQL Server人家也不干，人家也有这功能。

pg_trgm本身是不包含在Postgre SQL的源码安装中的，当然是插件的方式安装，安装上是很简单的，具体请百度

进入到你的数据库，create extension pg_trgm；就OK了

我在论坛中发现的第一个问题，是说建立这样的模糊查询，仅仅建立btree索引就可以了，但pg_trgm只支持两种索引Gist and Gin,这两种索引。（来自官方）

所以说正确的针对一个列的索引，是要建立两个索引的，一个是BTREE索引，一个是 GIN或Gist索引，两种索引面对的“客户”是不同的。

那就让我们来看看到底他是怎么工作得：

首先我们先生成一个表用来测试，创建一个存储过程用来插入测试数据  

create or replace function data_produce(int) returns text as $$    
declare    
  res text;    
begin    
  if $1 >=1 then    
    select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    
    return res;    
  end if;    
  return null;    
end;    
$$ language plpgsql strict;

insert into test_pg_trgm (search) select data_produce(20) from 
generate_series(1,1110000);

执行后生成我们本次要测试的数据10万条

下面我们创建索引了，创建GIN索引，创建索引中系统报错，这是由于还没有创建相关的扩展

添加了这些扩展后我们就可以建立相关的索引

我们可以看到查询已经走了索引，并且查询时间1ms

那如果我们没有这个索引会怎么样，这条语句慢了48倍并且只能和Oracle，SQL Server，MySQL一样走了全表扫描。

好，那如果已经体会到了PG在模糊查询中的厉害之处，群里有人问的第二个问题是GIN VS GIST那种索引更好

这也是一个热门的问题？

下面也做一个测试，（但不证明GIN比GIST性能强），我们建立一个gist的索引，也提通过查询来进行模糊方式的查询

图中的时间12ms ，比全表扫描快了4倍，比GIN慢了12倍

当然这里并不是说GIST不如GIN，具体的索引有不同的使用场景。

最后，我们来证明一下，普通的运算方式对于GIST GIN索引是无效的，所以我们对某个字段必须建立两个索引BTREE and GIST or GIN。

下图整体的证明了上面的立论。这里就不解释了

最后回归题目，PG为何“大爱”程序员，想想一个不靠谱的模糊需求能把一个程序员弄得“五脊六瘦”，而PG可以将这个事情化解，难道还不是程序员的“大爱”。

I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束
一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布