任何一个关系型数据库关于模糊匹配（like)的优化都是一件痛苦的事，相对而言，诸如like 'abc%'之类的还好一点，可以通过创建索引来优化，但对于like 'c%'之类的，真的就没有办法了。
这里介绍一种postgresql关于like 'c%'的优化方法，是基于全文检索的特性来实现的。
测试数据准备（环境centos6.5 + postgresql 9.6.1）。
postgres=# create table ts(id int,name text); CREATE TABLE postgres=# \d ts Table "public.ts" Column | Type | Modifiers --------+---------+----------- id | integer | name | text | postgres=# insert into ts select n,n||'_pjy' from generate_series(1,2000) n; INSERT 0 2000 postgres=# insert into ts select n,n||'_mdh' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_lmm' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_syf' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_wbd' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_hhh' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_sjw' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_jjs' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_ymd' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_biu' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# insert into ts select n,n||'_dfl' from generate_series(1,2000000) n; INSERT 0 2000000 postgres=# select count(*) from ts; count ---------- 20002000 (1 row)
开始测试：
postgres=# explain analyze select * from ts where name like '%pjy%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on ts (cost=0.00..358144.05 rows=2000 width=15) (actual time=0.006..1877.087 rows=2000 loops=1)
Filter: (name ~~ '%pjy%'::text)
Rows Removed by Filter: 20000000
Planning time: 0.031 ms
Execution time: 1877.178 ms
(5 rows)
关键一步：
postgres=# create index idx_name on ts using gin (to_tsvector('english',name));
CREATE INDEX
postgres=# vacuum analyze ts;
VACUUM
postgres=# \d ts
Table "public.ts"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"idx_name" gin (to_tsvector('english'::regconfig, name))
postgres=# explain analyze select * from ts where to_tsvector('english',name) @@ to_tsquery('pjy');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ts (cost=39.75..8187.70 rows=2000 width=15) (actual time=0.016..0.016 rows=0 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, name) @@ to_tsquery('pjy'::text))
-> Bitmap Index Scan on idx_name (cost=0.00..39.25 rows=2000 width=0) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (to_tsvector('english'::regconfig, name) @@ to_tsquery('pjy'::text))
Planning time: 0.094 ms
Execution time: 0.036 ms
(6 rows)
大家可以看到，执行时间从2秒下降到了0.04毫秒！！！
关于pg的全文检索，tsvector和tsquery，这里就不详细介绍了，大家可以自己查阅手册。
补充：postgresql子查询优化(提升子查询)
问题背景
在开发项目过程中，客户要求使用gbase8s数据库(基于informix)，简单的分页页面响应很慢。排查发现分页sql是先查询出数据在外面套一层后再取多少条，如果去掉嵌套的一层，直接获取则很快。日常使用中postgresql并没有这样的操作也很快，这是为什么呢？
说明
在数据库实现早期，查询优化器对子查询一般采用嵌套执行的方式，即父查询中的每一行，都要执行一次子查询，这样子查询会执行很多次，效率非常低。
本篇主要讲postgresql针对子查询的优化。
项目中使用子查询的地方非常多，如何写出高效的sql，掌握子查询的优化是非常有必要的。
执行计划对比（gbase8s vs postgresql）:
gbase8s慢sql执行计划：
--gbase8s执行计划
SET EXPLAIN ON ; SET EXPLAIN FILE TO '/home/gbasedbt/sqexplain.out' ; select skip 0 first 15 * from ( select * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc ) Estimated Cost: 3207 Estimated # of Rows Returned: 6172 "htmlcode">select skip 0 first 15 * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc "htmlcode">db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy_tjaj order by d_slrq limit 15 offset 0; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.44..28.17 rows=15 width=879) -> Index Scan using idx_ttjaj_dslrq on t_jcxxzy_tjaj (cost=0.44..32374439.85 rows=17507700 width=879) (2 rows) --子查询执行计划-嵌套一层 db_jcxxzypt=# explain db_jcxxzypt-# select * from ( db_jcxxzypt(# select * from db_jcxx.t_jcxxzy_tjaj order by d_slrq db_jcxxzypt(# )tab1 limit 15 offset 0; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.44..28.32 rows=15 width=879) -> Index Scan using idx_ttjaj_dslrq on t_jcxxzy_tjaj (cost=0.44..32374439.85 rows=17507700 width=879) (2 rows) "htmlcode">db_jcxxzypt=# explain select * from t_jcxxzy_tjaj aj ,(select * from t_jcxxzy_ajdsr) dsr where dsr.c_ajbm = '1301020400000120090101'; QUERY PLAN ------------------------------------------------------------------------- Nested Loop (cost=0.56..1252119.58 rows=17507700 width=1098) -> Index Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr (cost=0.56..8.57 rows=1 width=219) Index Cond: (c_ajbm = '1301020400000120090101'::bpchar) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1077034.00 rows=17507700 width=879) (4 rows) "htmlcode">db_jcxxzypt=# explain select * from t_jcxxzy_tjaj aj where aj.c_ajbm in (select dsr.c_ajbm from t_jcxxzy_ajdsr dsr); 转化为： select * from t_jcxxzy_tjaj aj join t_jcxxzy_ajdsr dsr aj.c_ajbm = dsr.c_ajbm; QUERY PLAN ------------------------------------------------------------------------- Hash Semi Join (cost=362618.61..5537768.07 rows=7957409 width=879) Hash Cond: (t_jcxxzy_tjaj.c_ajbm = t_jcxxzy_ajdsr.c_ajbm) -> Seq Scan on t_jcxxzy_tjaj (cost=0.00..1077034.00 rows=17507700 width=879) -> Hash (cost=237458.59..237458.59 rows=6817202 width=23) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr (cost=0.56..237458.59 rows=6817202 wi dth=23) (5 rows) --in等价于=any hash semi join表示执行的是两张表的hash半连接， 原始sql中没有(t_jcxxzy_tjaj.c_ajbm = t_jcxxzy_ajdsr.c_ajbm),表明此in子查询被优化，优化后采用hash semi join算法。 (2).相关子查询 --当加入条件where aj.d_slrq='2001-06-14'后不能提升子链接，如果把where aj.d_slrq ='2001-06-14'放到父查询 是支持子链接优化的 db_jcxxzypt=# explain db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where aj.d_slrq='2001-06-14') ; QUERY PLAN ------------------------------------------------------------------------- Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..2227874766580.75 rows=8753850 width=879) Filter: (SubPlan 1) SubPlan 1 -> Result (cost=0.56..237458.59 rows=6817202 width=23) One-Time Filter: (aj.d_slrq = '2001-06-14'::date) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr dsr (cost=0.56..237458.59 rows=6817 202 width=23) (6 rows (3). -- not in不能提升子链接 db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm not in (select c_ajbm from db_jcxx.t_jcxxzy_ajdsr); QUERY PLAN ------------------------------------------------------------------------- Seq Scan on t_jcxxzy_tjaj (cost=0.56..2875921362927.06 rows=8753850 width=879) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.56..311489.60 rows=6817202 width=23) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr (cost=0.56..237458.59 rows=6817202 width=23) (5 rows) --not in与<>all含义相同in子句存在不被优化的可能、当in子句中包含了主查询的表字段，和主查询有相关性时不能提升子链接。
exists子链接
--exists子链接
db_jcxxzypt=# explain db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where exists (select c_ajbm from t_jcxxzy_ajdsr dsr where aj.c_ajbm = dsr.c_ajbm); QUERY PLAN ------------------------------------------------------------------------- Hash Semi Join (cost=362618.61..5537768.07 rows=7957409 width=879) Hash Cond: (aj.c_ajbm = dsr.c_ajbm) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1077034.00 rows=17507700 width=879) -> Hash (cost=237458.59..237458.59 rows=6817202 width=23) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr dsr (cost=0.56..237458.59 rows=681720 2 width=23) (5 rows) -- 当加入where aj.c_xzdm = '150622'条件在子链接时，仍然支持上拉 db_jcxxzypt=# explain db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where exists (select c_ajbm from t_jcxxzy_ajdsr dsr where aj.c_xzdm = '150622'); QUERY PLAN ------------------------------------------------------------------------- Nested Loop Semi Join (cost=0.56..1361779.20 rows=5436 width=879) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1120803.25 rows=5436 width=879) Filter: ((c_xzdm)::text = '150622'::text) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr dsr (cost=0.56..237458.59 rows=6817202 widt h=0) (4 rows) --exists子链接 db_jcxxzypt=# explain db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where exists (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_ajbm='1101120300000120030101') db_jcxxzypt-# ; QUERY PLAN ------------------------------------------------------------------------- Result (cost=4.58..1077038.57 rows=17507700 width=879) One-Time Filter: $0 InitPlan 1 (returns $0) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr dsr (cost=0.56..4.58 rows=1 width=0) Index Cond: (c_ajbm = '1101120300000120030101'::bpchar) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1077034.00 rows=17507700 width=879) (6 rows)子查询只执行了一次，作为aj表的参数。
--not exists子链接 db_jcxxzypt=# explain db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where not exists (select c_ajbm from t_jcxxzy_ajdsr dsr); QUERY PLAN ------------------------------------------------------------------------- Result (cost=0.04..1077034.04 rows=17507700 width=879) One-Time Filter: (NOT $0) InitPlan 1 (returns $0) -> Seq Scan on t_jcxxzy_ajdsr dsr (cost=0.00..281210.02 rows=6817202 width=0) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1077034.00 rows=17507700 width=879) (5 rows)从执行计划上看，not exists子查询并没有被消除，子查询只是执行了一次，将结果作为aj表的参数。
in和exists都存在不被优化的可能，对于in和exists的选择，当父查询结果集小于子查询结果集则选择exists，如果父查询结果集大于子查询结果集选择in。
所有的all子链接都不支持上拉
db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm >all(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr); QUERY PLAN ------------------------------------------------------------------------- Seq Scan on t_jcxxzy_tjaj (cost=0.56..2875921362927.06 rows=8753850 width=879) Filter: (SubPlan 1) SubPlan 1 -> Materialize (cost=0.56..311489.60 rows=6817202 width=23) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr (cost=0.56..237458.59 rows=6817202 width=23) (5 rows) "htmlcode">db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm >some(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr); QUERY PLAN ------------------------------------------------------------------------- - Nested Loop Semi Join (cost=0.56..11316607.35 rows=5835900 width=879) -> Seq Scan on t_jcxxzy_tjaj (cost=0.00..1077034.00 rows=17507700 width=879) -> Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr (cost=0.56..64266.97 rows=2272401 width=23) Index Cond: (c_ajbm < t_jcxxzy_tjaj.c_ajbm) (4 rows) "htmlcode">db_jcxxzypt=# explain select * from t_jcxxzy_tjaj aj ,(select * from t_jcxxzy_ajdsr limit 10) dsr where dsr.c_ajbm = '1301020400000120090101'; QUERY PLAN ------------------------------------------------------------------------- Nested Loop (cost=0.00..1252111.54 rows=17507700 width=1098) -> Subquery Scan on dsr (cost=0.00..0.54 rows=1 width=219) Filter: (dsr.c_ajbm = '1301020400000120090101'::bpchar) -> Limit (cost=0.00..0.41 rows=10 width=219) -> Seq Scan on t_jcxxzy_ajdsr (cost=0.00..281210.02 rows=6817202 width=219) -> Seq Scan on t_jcxxzy_tjaj aj (cost=0.00..1077034.00 rows=17507700 width=879) (6 rows) "htmlcode">#from_collapse_limit = 8当from列表的对象少于from_collapse_limit时，优化器可以将子查询提升到上层进行JOIN，从而可能选择到更优的执行计划。
#join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses 当使用显示的JOIN时（除了full join），例如a join b join c join d，优化器可以重排JOIN的顺序，以产生更多的PLAN选择更优的执行计划。 如果join_collapse_limit=1，则不重排，使用SQL写法提供的顺序。 "htmlcode">val>all(select...) to val>max(select...) val<all(select...) to val<min(select...) val>any(select...) to val>min(select...) val<any(select...) to val<max(select...) val>=all(select...) to val>=max(select...) val<=all(select...) to val<=min(select...) val>=any(select...) to val>=min(select...) val<=any(select...) to val<=max(select...)通常，聚集函数min(),max()的执行效率要比any、all效率高
相关子查询和非相关子查询
相关子查询子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数，当父查询的参数改变时，子查询需要根据新参数值重新执行（查询优化器对相关子查询进行优化有一定意义），如：
select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_ajbm = aj.c_ajbm)/* 子查询语句中存在父查询的列 */非相关子查询子查询的执行，不依赖于外层父查询的任何属性值。这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解，如：
select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_xzdm = '150622')/* 子查询语句中不存在父查询的属性 */结束语
1.postgresql子查询的优化思路，子查询不用执行多次
2.优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序
3.子查询中的连接条件，过滤条件分别变成了父查询的连接条件、过滤条件、优化器可以对这些条件进行下推、提高执行效率
4.将子查询优化为表连接后，子查询只需要执行一次、而优化器可以根据统计信息来选择不同的连接方式和连接顺序、子查询的连接条件和过滤条件分别变成父查询的条件。
5.这些查询中all是完全不支持上拉子子链接的，in和exists存在不被优化的可能
6.not exists虽然没有被上拉，但是被优化为只执行一次，相对于not in稍好
7.可使用等价改写的方式优化
8.可根据配置文件，固化子查询，以及表的连接顺序
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com白云城资源网 Copyright www.dyhadc.com暂无“postgresql关于like%xxx%的优化操作”评论...一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5
这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。
一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。
另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！
昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？
更新日志
2024年11月05日2024年11月05日
- 雨林唱片《赏》新曲+精选集SACD版[ISO][2.3G]
- 罗大佑与OK男女合唱团.1995-再会吧!素兰【音乐工厂】【WAV+CUE】
- 草蜢.1993-宝贝对不起（国）【宝丽金】【WAV+CUE】
- 杨培安.2009-抒·情（EP）【擎天娱乐】【WAV+CUE】
- 周慧敏《EndlessDream》[WAV+CUE]
- 彭芳《纯色角3》2007[WAV+CUE]
- 江志丰2008-今生为你[豪记][WAV+CUE]
- 罗大佑1994《恋曲2000》音乐工厂[WAV+CUE][1G]
- 群星《一首歌一个故事》赵英俊某些作品重唱企划[FLAC分轨][1G]
- 群星《网易云英文歌曲播放量TOP100》[MP3][1G]
- 方大同.2024-梦想家TheDreamer【赋音乐】【FLAC分轨】
- 李慧珍.2007-爱死了【华谊兄弟】【WAV+CUE】
- 王大文.2019-国际太空站【环球】【FLAC分轨】
- 群星《2022超好听的十倍音质网络歌曲(163)》U盘音乐[WAV分轨][1.1G]
- 童丽《啼笑姻缘》头版限量编号24K金碟[低速原抓WAV+CUE][1.1G]