域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

任何一個(gè)關(guān)系型數(shù)據(jù)庫(kù)關(guān)于模糊匹配（like)的優(yōu)化都是一件痛苦的事，相對(duì)而言，諸如like 'abc%'之類的還好一點(diǎn)，可以通過(guò)創(chuàng)建索引來(lái)優(yōu)化，但對(duì)于like 'c%'之類的，真的就沒(méi)有辦法了。

這里介紹一種postgresql關(guān)于like 'c%'的優(yōu)化方法，是基于全文檢索的特性來(lái)實(shí)現(xiàn)的。

測(cè)試數(shù)據(jù)準(zhǔn)備（環(huán)境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)

開(kāi)始測(cè)試：

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)

關(guān)鍵一步：

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)

大家可以看到，執(zhí)行時(shí)間從2秒下降到了0.04毫秒！?。?/p>

關(guān)于pg的全文檢索，tsvector和tsquery，這里就不詳細(xì)介紹了，大家可以自己查閱手冊(cè)。

補(bǔ)充：postgresql子查詢優(yōu)化(提升子查詢)

問(wèn)題背景

在開(kāi)發(fā)項(xiàng)目過(guò)程中，客戶要求使用gbase8s數(shù)據(jù)庫(kù)(基于informix)，簡(jiǎn)單的分頁(yè)頁(yè)面響應(yīng)很慢。排查發(fā)現(xiàn)分頁(yè)sql是先查詢出數(shù)據(jù)在外面套一層后再取多少條，如果去掉嵌套的一層，直接獲取則很快。日常使用中postgresql并沒(méi)有這樣的操作也很快，這是為什么呢？

說(shuō)明

在數(shù)據(jù)庫(kù)實(shí)現(xiàn)早期，查詢優(yōu)化器對(duì)子查詢一般采用嵌套執(zhí)行的方式，即父查詢中的每一行，都要執(zhí)行一次子查詢，這樣子查詢會(huì)執(zhí)行很多次，效率非常低。

本篇主要講postgresql針對(duì)子查詢的優(yōu)化。

項(xiàng)目中使用子查詢的地方非常多，如何寫出高效的sql，掌握子查詢的優(yōu)化是非常有必要的。

執(zhí)行計(jì)劃對(duì)比（gbase8s vs postgresql）:

gbase8s慢sql執(zhí)行計(jì)劃：

--gbase8s執(zhí)行計(jì)劃

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 1) gbasedbt.t_szgl_jdry: INDEX PATH  (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetime    Index Keys: updatetime (Reverse) (Serial, fragments: ALL) QUERY: (OPTIMIZATION TIMESTAMP: 12-21-2017 03:20:43) ------ select skip 0 first 15 * from ( select * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc ) Estimated Cost: 232 Estimated # of Rows Returned: 6172 1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN Query statistics: ----------------- The final cost of the plan is reduced because of the FIRST n specification in the query. Table map : ---------------------------- Internal name   Table name ---------------------------- t1        t_szgl_jdry t2        (Temp Table For Collection Subquery) type   table rows_prod est_rows rows_scan time    est_cost ------------------------------------------------------------------- scan   t1   6173    6172   6173    00:00.05  3207  --查詢執(zhí)行用 222 ms,15行受影響

gbase8s修改后執(zhí)行計(jì)劃

1select skip 0 first 15 * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc Estimated Cost: 7 Estimated # of Rows Returned: 6172 1) gbasedbt.t_szgl_jdry: INDEX PATH (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetime Index Keys: updatetime (Reverse) (Serial, fragments: ALL) Query statistics: ----------------- The final cost of the plan is reduced because of the FIRST n specification in the query. Table map : ---------------------------- Internal name Table name ---------------------------- t1 t_szgl_jdry type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 15 6172 15 00:00.00 8 QUERY: (OPTIMIZATION TIMESTAMP: 12-21-2017 03:23:25) ------ select 1 from sysusers Estimated Cost: 2 Estimated # of Rows Returned: 1 1) gbasedbt.sysusers: SEQUENTIAL SCAN ... --查詢執(zhí)行用 18 ms,15行受影響

第一個(gè)執(zhí)行計(jì)劃中 (1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN)可以看出是將子查詢的結(jié)果查詢出來(lái)后，在這個(gè)基礎(chǔ)上獲取了15條記錄

對(duì)比postgresql執(zhí)行計(jì)劃

--分頁(yè)執(zhí)行計(jì)劃-不嵌套

1db_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) --子查詢執(zhí)行計(jì)劃-嵌套一層 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) --子查詢執(zhí)行計(jì)劃-嵌套兩層 db_jcxxzypt=# explain db_jcxxzypt-# select * from ( db_jcxxzypt(# select * from ( db_jcxxzypt(# select * from db_jcxx.t_jcxxzy_tjaj order by d_slrq db_jcxxzypt(# )tab1 )tab2 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)

postgresql的子查詢即使嵌套多層，執(zhí)行計(jì)劃還是和未嵌套一樣。原因就是postgresql在重寫sql的階段上拉子查詢（提升子查詢），把子查詢合并到父查詢中。

postgresql子查詢優(yōu)化

子查詢可分為三類:一、([not]in/all/any/some),二、([not]exists),三、其他子查詢(sjp子查詢 選擇、投影、連接)

子查詢可以出現(xiàn)在目標(biāo)列、form子句、where子句、join/on子句、group by子句、having子句、orderby子句等位置。

1db_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) Time: 1.101 ms

postgresql子鏈接([not]in,[not]exists,all,some,any)

子查詢和子鏈接區(qū)別：子查詢是不在表達(dá)式中的子句，子鏈接在表達(dá)式中的子句

--in子鏈接

(1).

1db_jcxxzypt=# explain select * from t_jcxxzy_tjaj aj where aj.c_ajbm in (select dsr.c_ajbm from t_jcxxzy_ajdsr dsr); 轉(zhuǎn)化為： 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等價(jià)于=any hash semi join表示執(zhí)行的是兩張表的hash半連接， 原始sql中沒(méi)有(t_jcxxzy_tjaj.c_ajbm = t_jcxxzy_ajdsr.c_ajbm),表明此in子查詢被優(yōu)化，優(yōu)化后采用hash semi join算法。 (2).相關(guān)子查詢 --當(dāng)加入條件where aj.d_slrq='2001-06-14'后不能提升子鏈接，如果把where aj.d_slrq ='2001-06-14'放到父查詢 是支持子鏈接優(yōu)化的 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子句存在不被優(yōu)化的可能、當(dāng)in子句中包含了主查詢的表字段，和主查詢有相關(guān)性時(shí)不能提升子鏈接。

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) -- 當(dāng)加入where aj.c_xzdm = '150622'條件在子鏈接時(shí)，仍然支持上拉 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)

子查詢只執(zhí)行了一次，作為aj表的參數(shù)。

1--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)

從執(zhí)行計(jì)劃上看，not exists子查詢并沒(méi)有被消除，子查詢只是執(zhí)行了一次，將結(jié)果作為aj表的參數(shù)。

in和exists都存在不被優(yōu)化的可能，對(duì)于in和exists的選擇，當(dāng)父查詢結(jié)果集小于子查詢結(jié)果集則選擇exists，如果父查詢結(jié)果集大于子查詢結(jié)果集選擇in。

所有的all子鏈接都不支持上拉

1db_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) 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) db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm <all(select -="" 1="" subplan="" 1)="" (subplan="" filter:="" width="879)" rows="8753850" (cost="0.56..2875921362927.06" t_jcxxzy_tjaj="" scan="" seq="" -------------------------------------------------------------------------="" plan="" query="" db_jcxx.t_jcxxzy_ajdsr);="" from="" c_ajbm="">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)

關(guān)于all的查詢都都是以子查詢的形式，不會(huì)上拉

some/any

--some和any是等效的

1db_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) 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 ------------------------------------------------------------------------- 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) db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm <some(select -="" width="879)" rows="5835900" (cost="0.56..11316607.35" -------------------------------------------------------------------------="" plan="" query="" db_jcxx.t_jcxxzy_ajdsr);="" from="" c_ajbm="" join="" semi="" loop="" nested="" ="">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) --some中未出現(xiàn)子查詢，dsr表都被上拉到父查詢中，與aj表進(jìn)行嵌套半連接和hash半連接

這些查詢中all是完全不支持上拉子子鏈接的，而in和exists存在不被上拉的可能。

不可上拉的子查詢

不支持帶有with子句的格式，集合操作、聚集函數(shù)(aggregates、group、distinct)、cte、having、limit/offset等子句格式

1db_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) Time: 0.958 ms

上拉子查詢后，父級(jí)的多個(gè)表之間的連接順序是怎么樣的呢？會(huì)有什么變化嗎？

對(duì)于被上拉的子查詢，abase把子查詢的關(guān)系并入主from-list中，這樣關(guān)系的個(gè)數(shù)會(huì)增加，按照多表連接順序算法就會(huì)產(chǎn)生更多的連接路徑比如A、B、C三張表的關(guān)聯(lián)就有{A,B}、{A,C}、{B,A}、{B,C}、{C,A}、{C,B}六種連接方式

join與子查詢固化或rewrite

join或子查詢的優(yōu)化，屬于優(yōu)化器優(yōu)化JOIN的范疇。

當(dāng)用戶的QUERY涉及到多個(gè)JOIN對(duì)象，或者涉及到多個(gè)子查詢時(shí)，優(yōu)化器可以選擇是否改變當(dāng)前的SQL，產(chǎn)生更多的plan選擇更優(yōu)的執(zhí)行計(jì)劃。

postgresql.conf文件中：

1#from_collapse_limit = 8

當(dāng)from列表的對(duì)象少于from_collapse_limit時(shí)，優(yōu)化器可以將子查詢提升到上層進(jìn)行JOIN，從而可能選擇到更優(yōu)的執(zhí)行計(jì)劃。

#join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses 當(dāng)使用顯示的JOIN時(shí)（除了full join），例如a join b join c join d，優(yōu)化器可以重排JOIN的順序，以產(chǎn)生更多的PLAN選擇更優(yōu)的執(zhí)行計(jì)劃。 如果join_collapse_limit=1，則不重排，使用SQL寫法提供的順序。 如果用戶要固化JOIN順序，請(qǐng)使用顯示的JOIN，同時(shí)將join_collapse_limit設(shè)置為1。 如果用戶不打算提升子查詢，同樣的，將from_collapse_limit 設(shè)置為1即可。

等價(jià)改寫

子查詢中沒(méi)有g(shù)roup by子句，也沒(méi)有聚集函數(shù)，則可使用下面的等價(jià)轉(zhuǎn)換

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...)

通常，聚集函數(shù)min(),max()的執(zhí)行效率要比any、all效率高

相關(guān)子查詢和非相關(guān)子查詢

相關(guān)子查詢子查詢的執(zhí)行依賴于外層父查詢的一些屬性值。子查詢因依賴于父查詢的參數(shù)，當(dāng)父查詢的參數(shù)改變時(shí)，子查詢需要根據(jù)新參數(shù)值重新執(zhí)行（查詢優(yōu)化器對(duì)相關(guān)子查詢進(jìn)行優(yōu)化有一定意義），如：

1select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_ajbm = aj.c_ajbm)/* 子查詢語(yǔ)句中存在父查詢的列 */

非相關(guān)子查詢子查詢的執(zhí)行，不依賴于外層父查詢的任何屬性值。這樣子查詢具有獨(dú)立性，可獨(dú)自求解，形成一個(gè)子查詢計(jì)劃先于外層的查詢求解，如：

1select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_xzdm = '150622')/* 子查詢語(yǔ)句中不存在父查詢的屬性 */

結(jié)束語(yǔ)

1.postgresql子查詢的優(yōu)化思路，子查詢不用執(zhí)行多次

2.優(yōu)化器可以根據(jù)統(tǒng)計(jì)信息來(lái)選擇不同的連接方法和不同的連接順序

3.子查詢中的連接條件，過(guò)濾條件分別變成了父查詢的連接條件、過(guò)濾條件、優(yōu)化器可以對(duì)這些條件進(jìn)行下推、提高執(zhí)行效率

4.將子查詢優(yōu)化為表連接后，子查詢只需要執(zhí)行一次、而優(yōu)化器可以根據(jù)統(tǒng)計(jì)信息來(lái)選擇不同的連接方式和連接順序、子查詢的連接條件和過(guò)濾條件分別變成父查詢的條件。

5.這些查詢中all是完全不支持上拉子子鏈接的，in和exists存在不被優(yōu)化的可能

6.not exists雖然沒(méi)有被上拉，但是被優(yōu)化為只執(zhí)行一次，相對(duì)于not in稍好

7.可使用等價(jià)改寫的方式優(yōu)化

8.可根據(jù)配置文件，固化子查詢，以及表的連接順序

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/204770.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！