postgresql通過索引優(yōu)化查詢速度操作
當(dāng)數(shù)據(jù)量比較大的時(shí)候，提升查詢效率就是需要去考慮的事情了。一個(gè)百萬級(jí)別的表格，如果不做任何優(yōu)化的話，即使是最簡單的查詢語句執(zhí)行起來也是慢的讓人難以接受；當(dāng)然“優(yōu)化”本身是一個(gè)比較復(fù)雜的工程，從設(shè)計(jì)表、字段到查詢語句的寫法都有很多講究，這里只考慮索引的方式，且是最普通的索引；
下面的操作中對(duì)應(yīng)數(shù)據(jù)庫表w008_execrise_info（8000數(shù)據(jù)量）， w008_wf02_info（4000數(shù)據(jù)量）
1 任務(wù)表數(shù)據(jù)
SELECT w.* FROM w008_wf02_info w WHERE w.is_removed =0 AND w.wfno = 'WF02' AND EXISTS ( SELECT 1 FROM w008_execrise_info info JOIN w008_privilege_allocation P ON ( info.subjecttyp = P.subjecttyp AND info.gradetyp = P.gradetyp AND P.loginname = w.create_by AND P.verifyusers ='yixi_li' AND P.wftype = 20 ) WHERE info.is_removed =0 AND info.wfid = w.wfid ) ORDER BY create_date DESC
執(zhí)行結(jié)果：耗時(shí)在3秒左右（這個(gè)數(shù)據(jù)量，這個(gè)速度就比較慢了）
順帶說一句，這里把w.* 改成對(duì)應(yīng)的字段也會(huì)稍微的提高一些查詢速度（畢竟少了一步把*轉(zhuǎn)成對(duì)應(yīng)字段的操作），在標(biāo)準(zhǔn)的查詢規(guī)范中也不會(huì)寫成table.*這種方式的。
2 添加索引：
雖說索引可以提高查詢速度，但是不代表加了索引就一定會(huì)加快查詢速度，有時(shí)甚至?xí)m得其反。
一般來說索引會(huì)加在where 后面的查詢字段，尤其是關(guān)聯(lián)字段上面，這里w008_execrise_info 表數(shù)據(jù)量最大，暫時(shí)針對(duì)這個(gè)表進(jìn)行處理；w008_execrise_info 表涉及四個(gè)字段：subjecttyp，gradetyp，wfid和is_removed。
其中is_removed先不考慮，subjecttyp和gradetyp保存的是字典表的數(shù)據(jù)（數(shù)據(jù)內(nèi)容比較少，類似10 20 30 40），wfid保存的是數(shù)字類型的字符串。
一個(gè)索引起到的效果還取決于這樣一個(gè)條件，一般來說添加索引的字段的值"唯一性"越明顯越好，在這里，subjecttyp和gradetyp包含大量的重復(fù)值，索引效果會(huì)“不明顯”一些，而wfid 更像是“主鍵”，相對(duì)應(yīng)的效果會(huì)好很多。
2.1 subjecttyp 和gradetyp添加索引
CREATE INDEX w008_execrise_info_gradetyp_index ON w008_execrise_info (gradetyp);
CREATE INDEX w008_execrise_info_subjecttyp_index ON w008_execrise_info (subjecttyp);
添加后執(zhí)行時(shí)間大約2秒，快了一點(diǎn)點(diǎn)
2.2 對(duì)wfid添加索引
CREATE INDEX w008_execrise_info_wfid_index ON w008_execrise_info (wfid);
再次執(zhí)行，0.2秒左右，快了很多
再補(bǔ)充一句，有很多情況下索引是不起作用的，比如 like后面跟的字段，還有條件語句or關(guān)聯(lián)的字段，這種情況就是要考慮查詢策略了。
3 查看當(dāng)前表的索引內(nèi)容；
select * from pg_indexes where tablename='w008_execrise_info';
select * from pg_statio_all_indexes where relname='w008_execrise_info';
4 刪除索引
DROP INDEX indexName;
5 重置索引
對(duì)于一些經(jīng)常改動(dòng)的表，如果時(shí)間長了發(fā)現(xiàn)查詢效率變慢，可以考慮重置一下索引；
因?yàn)槿绻淼膬?nèi)容被頻繁的修改的話會(huì)產(chǎn)生許多類似'索引碎片'的東西，會(huì)導(dǎo)致查詢索引本身的時(shí)間變長；
REINDEX INDEX index_name;//重置單個(gè)索引 REINDEX TABLE table_name;//重置整個(gè)表的索引 REINDEX DATABASE db_name;//終止整個(gè)數(shù)據(jù)庫的所以你
補(bǔ)充：PostgreSql查詢優(yōu)化之根據(jù)執(zhí)行計(jì)劃優(yōu)化SQL
1、執(zhí)行計(jì)劃路徑選擇
postgresql查詢規(guī)劃過程中，查詢請(qǐng)求的不同執(zhí)行方案是通過建立不同的路徑來表達(dá)的，在生成許多符合條件的路徑之后，要從中選擇出代價(jià)最小的路徑（基于成本運(yùn)算），把它轉(zhuǎn)化為一個(gè)計(jì)劃，傳遞給執(zhí)行器執(zhí)行，規(guī)劃器的核心工作就是生成多條路徑，然后從中找出最優(yōu)的那一條。
1.1代價(jià)評(píng)估
評(píng)估路徑優(yōu)劣的依據(jù)是用系統(tǒng)表pg_statistic中的統(tǒng)計(jì)信息估算出來的不同路徑的代價(jià)(cost)，PostgreSQL估計(jì)計(jì)劃成本的方式：基于統(tǒng)計(jì)信息估計(jì)計(jì)劃中各個(gè)節(jié)點(diǎn)的成本。PostgreSQL會(huì)分析各個(gè)表來獲取一個(gè)統(tǒng)計(jì)信息樣本（這個(gè)操作通常是由autovacuum這個(gè)守護(hù)進(jìn)程周期性的執(zhí)行analyze，來收集這些統(tǒng)計(jì)信息，然后保存到pg_statistic和pg_class里面）。
1.2用于估算代價(jià)的參數(shù)postgresql.conf
# - Planner Cost Constants - #seq_page_cost = 1.0 # measured on an arbitrary scale 順序磁盤掃描時(shí)單個(gè)頁面的開銷 #random_page_cost = 4.0 # same scale as above 隨機(jī)磁盤訪問時(shí)單頁面的讀取開銷 #cpu_tuple_cost = 0.01 # same scale as above cpu處理每一行的開銷 #cpu_index_tuple_cost = 0.005 # same scale as above cpu處理每個(gè)索引行的開銷 #cpu_operator_cost = 0.0025 # same scale as above cpu處理每個(gè)運(yùn)算符或者函數(shù)調(diào)用的開銷 #parallel_tuple_cost = 0.1 # same scale as above 計(jì)算并行處理的成本，如果成本高于非并行，則不會(huì)開啟并行處理。 #parallel_setup_cost = 1000.0 # same scale as above #min_parallel_relation_size = 8MB #effective_cache_size = 4GB 再一次索引掃描中可用的文件系統(tǒng)內(nèi)核緩沖區(qū)有效大小
也可以使用 show all的方式查看
1.3 路徑的選擇
--查看表信息
highgo=# \d t_jcxxgl_tjaj Table "db_jcxx.t_jcxxgl_tjaj" Column | Type | Modifiers --------------+--------------------------------+----------- c_bh | character(32) | not null c_xzdm | character varying(300) | c_jgid | character(32) | c_ajbm | character(22) | ... Indexes: "t_jcxxgl_tjaj_pkey" PRIMARY KEY, btree (c_bh) "idx_ttjaj_cah" btree (c_ah) "idx_ttjaj_dslrq" btree (d_slrq)
首先更新統(tǒng)計(jì)信息vacuum analyze t_jcxxgl_tjaj，許多時(shí)候可能因?yàn)榻y(tǒng)計(jì)信息的不準(zhǔn)確導(dǎo)致了不正常的執(zhí)行計(jì)劃--執(zhí)行計(jì)劃。
--執(zhí)行計(jì)劃，全表掃描
highgo=# explain (analyze,verbose,costs,buffers,timing)select c_bh,c_xzdm,c_jgid,c_ajbm from t_jcxxgl_tjaj where d_slrq >='2018-03-18';
QUERY PLAN ------------------------------------------------------------------------------------------------------------
Seq Scan on db_jcxx.t_jcxxgl_tjaj (cost=0.00..9.76 rows=3 width=96) (actual time=1.031..1.055 rows=3 loops
=1)
Output: c_bh, c_xzdm, c_jgid, c_ajbm
Filter: (t_jcxxgl_tjaj.d_slrq >= '2018-03-18'::date)
Rows Removed by Filter: 138
Buffers: shared hit=8
Planning time: 6.579 ms
Execution time: 1.163 ms
(7 rows)
如上，d_slrq是有索引的，但是執(zhí)行計(jì)劃中并沒有走索引，為什么呢？我們繼續(xù)往下看。
--執(zhí)行計(jì)劃，關(guān)閉全表掃描
highgo=# set session enable_seqscan = off;
SET
highgo=# explain (analyze,verbose,costs,buffers,timing)select c_bh,c_xzdm,c_jgid,c_ajbm from t_jcxxgl_tjaj where d_slrq >='2018-03-18';
QUERY PLAN ------------------------------------------------------------------------------------------------------------
Index Scan using idx_ttjaj_dslrq on db_jcxx.t_jcxxgl_tjaj (cost=0.14..13.90 rows=3 width=96) (actual time=0.012..0.026 rows=3 loops=1)
Output: c_bh, c_xzdm, c_jgid, c_ajbm
Index Cond: (t_jcxxgl_tjaj.d_slrq >= '2018-03-18'::date)
Buffers: shared hit=4
Planning time: 0.309 ms
Execution time: 0.063 ms
(6 rows)
d_slrq上面有btree索引，但是查看執(zhí)行計(jì)劃并沒有走索引，這是為什么呢？
代價(jià)計(jì)算：
一個(gè)路徑的估算由三部分組成：啟動(dòng)代價(jià)(startup cost)，總代價(jià)(totalcost)，執(zhí)行結(jié)果的排序方式(pathkeys)
代價(jià)估算公式：
總代價(jià)=啟動(dòng)代價(jià)+I/O代價(jià)+CPU代價(jià)（cost=S+P+W*T）
P:執(zhí)行時(shí)要訪問的頁面數(shù)，反應(yīng)磁盤的I/O次數(shù)
T:表示在執(zhí)行時(shí)所要訪問的元組數(shù)，反映了cpu開銷
W:表示磁盤I/O代價(jià)和CPU開銷建的權(quán)重因子
統(tǒng)計(jì)信息：
統(tǒng)計(jì)信息的其中一部分是每個(gè)表和索引中項(xiàng)的總數(shù)，以及每個(gè)表和索引占用的磁盤塊數(shù)。這些信息保存在pg_class表的reltuples和relpages列中。我們可以這樣查詢相關(guān)信息：
--查看統(tǒng)計(jì)信息
highgo=# select relpages,reltuples from pg_class where relname ='t_jcxxgl_tjaj'; relpages | reltuples ----------+----------- 8 | 141 (1 row)
total_cost = 1(seq_page_cost)*8(磁盤總頁數(shù))+0.01(cpu_tuple_cost)*141(表的總記錄數(shù))+0.0025(cpu_operation_cost)*141(表的總記錄數(shù))=9.7625
可以看到走索引的cost=13.90比全表掃描cost=9.76要大。所以上面沒有關(guān)閉全表掃描的時(shí)候，根據(jù)成本代價(jià)，執(zhí)行計(jì)劃走的全表掃描。在表較小的情況下，全表掃描比索引掃描更有效， index scan 至少要發(fā)生兩次I/O，一次是讀取索引塊，一次是讀取數(shù)據(jù)塊。
2、一個(gè)SQL優(yōu)化實(shí)例
2.1慢SQL：
select c_ajbh, c_ah, c_cbfy, c_cbrxm, d_larq, d_jarq, n_dbjg, c_yqly from db_zxzhld.t_zhld_db dbxx join db_zxzhld.t_zhld_ajdbxx dbaj on dbxx.c_bh = dbaj.c_dbbh where dbxx.n_valid=1 and dbxx.n_state in (1,2,3) and dbxx.c_dbztbh='1003' and dbaj.c_zblx='1003' and dbaj.c_dbfy='0' and dbaj.c_gy = '2550' and c_ajbh in (select distinct c_ajbh from db_zxzhld.t_zhld_zbajxx where n_dbzt = 1 and c_zblx = '1003' and c_gy = '2550' ) order by d_larq asc, c_ajbh asc limit 15 offset 0;
慢sql耗時(shí)：7s
先過下這個(gè)sql是干什么的、首先dbxx和dbaj的一個(gè)join連接然后dbaj.c_ajbh要包含在zbaj表里面，做了個(gè)排序，取了15條記錄、大概就這樣。
Sql有個(gè)缺點(diǎn)就是我不知道查詢的字段是從那個(gè)表里面取的、建議加上表別名.字段。
查看該sql的表的數(shù)據(jù)量：
t_zhld_db ：1311 t_zhld_ajdbxx ：341296 t_zhld_zbajxx ：1027619
執(zhí)行計(jì)劃：
Limit (cost=36328.67..36328.68 rows=1 width=107) (actual time=88957.677..88957.729 rows=15 loops=1)
-> Sort (cost=36328.67..36328.68 rows=1 width=107) (actual time=88957.653..88957.672 rows=15 loops=1)
Sort Key: dbaj.d_larq, dbaj.c_ajbh
Sort Method: top-N heapsort Memory: 27kB
-> Nested Loop Semi Join (cost=17099.76..36328.66 rows=1 width=107) (actual time=277.794..88932.662 rows=8605 loops=1)
Join Filter: ((dbaj.c_ajbh)::text = (t_zhld_zbajxx.c_ajbh)::text)
Rows Removed by Join Filter: 37018710
-> Nested Loop (cost=0.00..19200.59 rows=1 width=107) (actual time=199.141..601.845 rows=8605 loops=1)
Join Filter: (dbxx.c_bh = dbaj.c_dbbh)
Rows Removed by Join Filter: 111865
-> Seq Scan on t_zhld_ajdbxx dbaj (cost=0.00..19117.70 rows=219 width=140) (actual time=198.871..266.182 rows=8605 loops=1)
Filter: ((n_valid = 1) AND ((c_zblx)::text = '1003'::text) AND ((c_dbfy)::text = '0'::text) AND ((c_gy)::text = '2550'::text))
Rows Removed by Filter: 332691
-> Materialize (cost=0.00..66.48 rows=5 width=33) (actual time=0.001..0.017 rows=14 loops=8605)
-> Seq Scan on t_zhld_db dbxx (cost=0.00..66.45 rows=5 width=33) (actual time=0.044..0.722 rows=14 loops=1)
Filter: ((n_valid = 1) AND ((c_dbztbh)::text = '1003'::text) AND (n_state = ANY ('{1,2,3}'::integer[])))
Rows Removed by Filter: 1297
-> Materialize (cost=17099.76..17117.46 rows=708 width=32) (actual time=0.006..4.890 rows=4303 loops=8605)
-> HashAggregate (cost=17099.76..17106.84 rows=708 width=32) (actual time=44.011..54.924 rows=8605 loops=1)
Group Key: t_zhld_zbajxx.c_ajbh
-> Bitmap Heap Scan on t_zhld_zbajxx (cost=163.36..17097.99 rows=708 width=32) (actual time=5.218..30.278 rows=8605 loops=1)
Recheck Cond: ((n_dbzt = 1) AND ((c_zblx)::text = '1003'::text))
Filter: ((c_gy)::text = '2550'::text)
Rows Removed by Filter: 21849
Heap Blocks: exact=960
-> Bitmap Index Scan on i_tzhldzbajxx_zblx_dbzt (cost=0.00..163.19 rows=5876 width=0) (actual time=5.011..5.011 rows=30458 loops=1)
Index Cond: ((n_dbzt = 1) AND ((c_zblx)::text = '1003'::text))
Planning time: 1.258 ms
Execution time: 88958.029 ms
執(zhí)行計(jì)劃解讀：
1：第27->21行，通過索引i_tzhldzbajxx_zblx_dbzt過濾表t_zhld_zbajxx的數(shù)據(jù)，然后根據(jù)過濾條件(c_gy)::text = '2550'::text過濾最終返回8605條數(shù)據(jù)
2：第17->15行，根據(jù)條件過濾t_zhld_db表的數(shù)據(jù)，最終返回了14條數(shù)據(jù)
3：第20->19行，對(duì)表t_zhld_zbajxx做group by的操作
4：第13->11行，全表掃描t_zhld_ajdbxx 最終返回了8605條數(shù)據(jù)
5：第08行，根據(jù)t_zhld_ajdbxx返回的8605條結(jié)果集作為驅(qū)動(dòng)表和t_zhld_db的結(jié)果集(14條)做嵌套循環(huán)，t_zhld_db的結(jié)果集被循環(huán)了8605次。然后過濾掉了其中的111865條記錄，那么最終將得到（8605*14-111865） = 8605
6：第07->05行，根據(jù)第08和18行返回的結(jié)果集最終做了Nested Loop Semi Join，第18行的4303條結(jié)果集被循環(huán)了8605次,(4303*8605-37018710)=8605
7: 第04->02行，對(duì)最終的8605條記錄進(jìn)行排序
8：第01行，limit最終獲取15條記錄
整個(gè)執(zhí)行計(jì)劃中耗時(shí)最長的地方在05行Nested Loop Semi Join，actual time=277.794..88932.662， 表db_zxzhld.t_zhld_db dbxx和db_zxzhld.t_zhld_ajdbxx均是全表掃描
2.2具體優(yōu)化步驟
查看索引頁并沒有索引，創(chuàng)建c_ajbh，c_dbbh等邏輯外鍵的索引
drop index if exists I_T_ZHLD_AJDBXX_AJBH; create index I_T_ZHLD_AJDBXX_AJBH on T_ZHLD_AJDBXX (c_ajbh); commit; drop index if exists I_T_ZHLD_AJDBXX_DBBH; create index I_T_ZHLD_AJDBXX_DBBH on T_ZHLD_AJDBXX (c_dbbh); commit;
創(chuàng)建d_larq,c_ajbh的排序索引：
drop index if exists I_T_ZHLD_AJDBXX_m6;create index I_T_ZHLD_AJDBXX_m6 on T_ZHLD_AJDBXX (c_zblx,c_dbfy,c_gy,d_larq asc,c_ajbh asc); commit; drop index if exists I_T_ZHLD_ZBAJXX_h3 ; create index I_T_ZHLD_ZBAJXX_h3 on db_zxzhld.t_zhld_zbajxx (n_dbzt,c_zblx,c_gy,c_gy); commit;
創(chuàng)建索引后執(zhí)行計(jì)劃有了改變，原來的dbaj表和dbxx表先做nestedloop變成了zbaj和dbaj表先做了nestedloop join,總的cost也從36328.68降到了12802.87，
執(zhí)行計(jì)劃
Limit (cost=12802.87..12802.87 rows=1 width=107) (actual time=4263.598..4263.648 rows=15 loops=1)
-> Sort (cost=12802.87..12802.87 rows=1 width=107) (actual time=4263.592..4263.609 rows=15 loops=1)
Sort Key: dbaj.d_larq, dbaj.c_ajbh
Sort Method: top-N heapsort Memory: 27kB
-> Nested Loop (cost=2516.05..12802.86 rows=1 width=107) (actual time=74.240..4239.723 rows=8605 loops=1)
Join Filter: (dbaj.c_dbbh = dbxx.c_bh)
Rows Removed by Join Filter: 111865
-> Nested Loop (cost=2516.05..12736.34 rows=1 width=140) (actual time=74.083..327.974 rows=8605 loops=1)
-> HashAggregate (cost=2515.62..2522.76 rows=714 width=32) (actual time=74.025..90.185 rows=8605 loops=1)
Group Key: ("ANY_subquery".c_ajbh)::text
-> Subquery Scan on "ANY_subquery" (cost=2499.56..2513.84 rows=714 width=32) (actual time=28.782..59.823 rows=8605 loops=1)
-> HashAggregate (cost=2499.56..2506.70 rows=714 width=32) (actual time=28.778..39.968 rows=8605 loops=1)
Group Key: zbaj.c_ajbh
-> Index Scan using i_t_zhld_zbajxx_h3 on t_zhld_zbajxx zbaj (cost=0.42..2497.77 rows=715 width=32) (actual time=0.062..15.104 rows=8605 loops=1)
Index Cond: ((n_dbzt = 1) AND ((c_zblx)::text = '1003'::text) AND ((c_gy)::text = '2550'::text))
-> Index Scan using i_t_zhld_ajdbxx_ajbh on t_zhld_ajdbxx dbaj (cost=0.42..14.29 rows=1 width=140) (actual time=0.015..0.021 rows=1 loops=8605)
Index Cond: ((c_ajbh)::text = ("ANY_subquery".c_ajbh)::text)
Filter: (((c_zblx)::text = '1003'::text) AND ((c_dbfy)::text = '0'::text) AND ((c_gy)::text = '2550'::text))
Rows Removed by Filter: 1
-> Seq Scan on t_zhld_db dbxx (cost=0.00..66.45 rows=5 width=33) (actual time=0.015..0.430 rows=14 loops=8605)
Filter: ((n_valid = 1) AND ((c_dbztbh)::text = '1003'::text) AND (n_state = ANY ('{1,2,3}'::integer[])))
Rows Removed by Filter: 1298
Planning time: 1.075 ms
Execution time: 4263.803 ms
執(zhí)行的時(shí)間還是要4s左右仍然不滿足需求，并且沒有使用上I_T_ZHLD_AJDBXX_m6這個(gè)索引。
2.3等價(jià)改寫SQL(1)
等價(jià)改寫：將排序條件加入db_zxzhld.t_zhld_ajdbxx讓其先排序，再和t_zhld_db表連接。
修改后sql：
Select dbaj.c_ajbh, dbaj.c_ah, dbaj.c_cbfy, dbaj.c_cbrxm, dbaj.d_larq, dbaj.d_jarq, dbaj.n_dbjg, dbaj.c_yqly from (select * from db_zxzhld.t_zhld_db where n_valid=1 and n_state in (1,2,3) and c_dbztbh='1003' )dbxx join (select * from db_zxzhld.t_zhld_ajdbxx where n_valid=1 and c_zblx='1003' and c_dbfy='0' and c_gy = '2550' and c_ajbh in (select distinct c_ajbh from db_zxzhld.t_zhld_zbajxx where n_dbzt = 1 and c_zblx = '1003' and c_gy = '2550' ) order by d_larq asc, c_ajbh asc)dbajon dbxx.c_bh = dbaj.c_dbbh limit 15 offset 0
再次查看執(zhí)行計(jì)劃：
Limit (cost=3223.92..3231.97 rows=1 width=107) (actual time=127.291..127.536 rows=15 loops=1)
-> Nested Loop (cost=3223.92..3231.97 rows=1 width=107) (actual time=127.285..127.496 rows=15 loops=1)
-> Sort (cost=3223.64..3223.65 rows=1 width=140) (actual time=127.210..127.225 rows=15 loops=1)
Sort Key: t_zhld_ajdbxx.d_larq, t_zhld_ajdbxx.c_ajbh
Sort Method: quicksort Memory: 2618kB
-> Hash Semi Join (cost=2523.19..3223.63 rows=1 width=140) (actual time=55.913..107.265 rows=8605 loops=1)
Hash Cond: ((t_zhld_ajdbxx.c_ajbh)::text = (t_zhld_zbajxx.c_ajbh)::text)
-> Index Scan using i_t_zhld_ajdbxx_m6 on t_zhld_ajdbxx (cost=0.42..700.28 rows=219 width=140) (actual time=0.065..22.005 rows=8605 loops=1)
Index Cond: (((c_zblx)::text = '1003'::text) AND ((c_dbfy)::text = '0'::text) AND ((c_gy)::text = '2550'::text))
-> Hash (cost=2513.84..2513.84 rows=714 width=32) (actual time=55.802..55.802 rows=8605 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 675kB
-> HashAggregate (cost=2499.56..2506.70 rows=714 width=32) (actual time=30.530..43.275 rows=8605 loops=1)
Group Key: t_zhld_zbajxx.c_ajbh
-> Index Scan using i_t_zhld_zbajxx_h3 on t_zhld_zbajxx (cost=0.42..2497.77 rows=715 width=32) (actual time=0.043..15.552 rows=8605 loops=1)
Index Cond: ((n_dbzt = 1) AND ((c_zblx)::text = '1003'::text) AND ((c_gy)::text = '2550'::text))
-> Index Scan using t_zhld_db_pkey on t_zhld_db (cost=0.28..8.30 rows=1 width=33) (actual time=0.009..0.011 rows=1 loops=15)
Index Cond: (c_bh = t_zhld_ajdbxx.c_dbbh)
Filter: (((c_dbztbh)::text = '1003'::text) AND (n_state = ANY ('{1,2,3}'::integer[])))
Planning time: 1.154 ms
Execution time: 127.734 ms
這一次可以看出，ajdbxx和zbajxx表做了hash semi join 消除了nestedloop,cost降到了3231.97。并且使用上了i_t_zhld_ajdbxx_m6子查詢中in的結(jié)果集有一萬多條數(shù)據(jù)。
繼續(xù)嘗試使用exists等價(jià)改寫in，看能否有更好的結(jié)果
2.4等價(jià)改寫SQL(2)
等價(jià)改寫：將in替換為exists：
select c_ajbh, c_ah, c_cbfy, c_cbrxm, d_larq, d_jarq, n_dbjg, c_yqlyfrom (select c_bh from db_zxzhld.t_zhld_db where n_state in (1,2,3) and c_dbztbh='1003' )dbxx join (select c_ajbh, c_ah, c_cbfy, c_cbrxm, d_larq, d_jarq, n_dbjg, c_yqly,c_dbbh from db_zxzhld.t_zhld_ajdbxx ajdbxxwhere c_zblx='1003' and c_dbfy='0' and c_gy = '2550' and exists (select distinct c_ajbh from db_zxzhld.t_zhld_zbajxx zbajxx where ajdbxx.c_ajbh = zbajxx.c_ajbh and n_dbzt = 1 and c_zblx = '1003' and c_gy = '2550' ) order by d_larq asc, c_ajbh asc)dbajon dbxx.c_bh = dbaj.c_dbbh limit 15 offset 0
再次查看執(zhí)行計(jì)劃：
Limit (cost=1.12..2547.17 rows=1 width=107) (actual time=0.140..0.727 rows=15 loops=1)
-> Nested Loop (cost=1.12..2547.17 rows=1 width=107) (actual time=0.136..0.689 rows=15 loops=1)
-> Nested Loop Semi Join (cost=0.85..2538.84 rows=1 width=140) (actual time=0.115..0.493 rows=15 loops=1)
-> Index Scan using i_t_zhld_ajdbxx_m6 on t_zhld_ajdbxx t2 (cost=0.42..700.28 rows=219 width=140) (actual time=0.076..0.127 rows=15 loops=1)
Index Cond: (((c_zblx)::text = '1003'::text) AND ((c_dbfy)::text = '0'::text) AND ((c_gy)::text = '2550'::text))
-> Index Scan using i_t_zhld_zbajxx_c_ajbh on t_zhld_zbajxx t3 (cost=0.42..8.40 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=15)
Index Cond: ((c_ajbh)::text = (t2.c_ajbh)::text)
Filter: (((c_zblx)::text = '1003'::text) AND ((c_gy)::text = '2550'::text) AND (n_dbzt = 1))
-> Index Scan using t_zhld_db_pkey on t_zhld_db (cost=0.28..8.30 rows=1 width=33) (actual time=0.007..0.008 rows=1 loops=15)
Index Cond: (c_bh = t2.c_dbbh)
Filter: (((c_dbztbh)::text = '1003'::text) AND (n_state = ANY ('{1,2,3}'::integer[])))
Planning time: 1.268 ms
Execution time: 0.859 ms
可以看出使用exist效果更好，最終cost 2547.17
(1).少了t_zhld_zbajxx表的group by操作：Sort Key: t_zhld_ajdbxx.d_larq, t_zhld_ajdbxx.c_ajbh。(這一步是因?yàn)槭褂昧怂饕械呐判?
(2).少了分組的操作：Group Key: t_zhld_zbajxx.c_ajbh。
第(2)為什么這個(gè)查詢消除了t_zhld_zbajxx表的group by操作呢？
原因是exists替換了distinct的功能，一旦滿足條件則立刻返回。所以使用exists的時(shí)候子查詢可以直接去掉distinct。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
在使用PostgreSQL數(shù)據(jù)庫時(shí),忘記數(shù)據(jù)庫密碼可能會(huì)影響到正常的開發(fā)和維護(hù)工作,本文將詳細(xì)給大家介紹了PostgreSQL修改用戶密碼的多種方式,并有相關(guān)的代碼示例供大家參考,需要的朋友可以參考下2025-05-05
這篇文章主要介紹了PostgreSQL 邏輯復(fù)制 配置操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 中的加密擴(kuò)展插件pgcrypto用法說明
這篇文章主要介紹了postgresql 中的加密擴(kuò)展插件pgcrypto用法說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出的操作代碼
在數(shù)據(jù)庫管理中,數(shù)據(jù)的導(dǎo)入和導(dǎo)出是非常常見的操作,特別是在 PostgreSQL 中,提供了多種工具和方法來實(shí)現(xiàn)數(shù)據(jù)的有效管理,本文將詳細(xì)介紹在 PostgreSQL 中如何進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出,并給出具體的命令及示例,需要的朋友可以參考下2024-10-10
PostgreSQL中rank()窗口函數(shù)實(shí)用指南與示例
在數(shù)據(jù)分析和數(shù)據(jù)庫管理中,經(jīng)常需要對(duì)數(shù)據(jù)進(jìn)行排名操作,PostgreSQL提供了強(qiáng)大的窗口函數(shù)rank(),可以方便地對(duì)結(jié)果集中的行進(jìn)行排名,本文將詳細(xì)介紹rank()函數(shù)的使用方法,并通過多個(gè)實(shí)用示例展示其在不同場(chǎng)景下的應(yīng)用,需要的朋友可以參考下2025-07-07
PostgreSQL中調(diào)用存儲(chǔ)過程并返回?cái)?shù)據(jù)集實(shí)例
這篇文章主要介紹了PostgreSQL中調(diào)用存儲(chǔ)過程并返回?cái)?shù)據(jù)集實(shí)例,本文給出一創(chuàng)建數(shù)據(jù)表、插入測(cè)試數(shù)據(jù)、創(chuàng)建存儲(chǔ)過程、調(diào)用創(chuàng)建存儲(chǔ)過程和運(yùn)行效果完整例子,需要的朋友可以參考下2015-01-01
PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志
在?般的?產(chǎn)環(huán)境中,數(shù)據(jù)庫都需要開啟歸檔模式,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05
PostgreSql 導(dǎo)入導(dǎo)出sql文件格式的表數(shù)據(jù)實(shí)例
這篇文章主要介紹了PostgreSql 導(dǎo)入導(dǎo)出sql文件格式的表數(shù)據(jù)實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL物理備份恢復(fù)之 pg_rman的用法說明
這篇文章主要介紹了PostgreSQL物理備份恢復(fù)之 pg_rman的用法說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02
PostgreSQL 作為一款功能強(qiáng)大的開源關(guān)系型數(shù)據(jù)庫,其復(fù)制功能對(duì)于構(gòu)建高可用性系統(tǒng)至關(guān)重要,本文給大家詳細(xì)介紹了PostgreSQL的復(fù)制參數(shù),需要的朋友可以參考下2025-05-05