PG 10在分區(qū)表上執(zhí)行查詢時(shí)，會(huì)逐個(gè)檢查每個(gè)分區(qū)的約束來看是否需要，如果分區(qū)很多在計(jì)劃階段會(huì)有較大的性能損失。PG 11通過”partition pruning“算法來快速的標(biāo)識(shí)匹配的分區(qū)來改進(jìn)性能，但PG 11仍然做了一些不必要的處理比如不管是否涉及仍然加載了所有分區(qū)的元數(shù)據(jù)。?
PG 12更進(jìn)一步，那就是在pruning后才加載元數(shù)據(jù)，如果不涉及大多數(shù)的分區(qū)那么在計(jì)劃階段可以帶來明顯的性能提升。

站在用戶的角度思考問題，與客戶深入溝通，找到汪清網(wǎng)站設(shè)計(jì)與汪清網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：做網(wǎng)站、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名注冊(cè)、網(wǎng)頁(yè)空間、企業(yè)郵箱。業(yè)務(wù)覆蓋汪清地區(qū)。

創(chuàng)建分區(qū)表

[local]:5432?pg12@testdb=#?drop?table?if?exists?t_counter;NOTICE:??table?"t_counter"?does?not?exist,?skippingDROP?TABLETime:?29.768?ms[local]:5432?pg12@testdb=#?create?table?t_counter(id?int);CREATE?TABLETime:?120.165?ms[local]:5432?pg12@testdb=#?insert?into?t_counter?select?generate_series(0,100000);INSERT?0?100001Time:?333.637?ms[local]:5432?pg12@testdb=#?drop?table?if?exists?t_hash_manypartitions;NOTICE:??table?"t_hash_manypartitions"?does?not?exist,?skippingDROP?TABLETime:?1.536?ms[local]:5432?pg12@testdb=#?create?table?t_hash_manypartitions?(c1?int,c2??varchar(40),c3?varchar(40))?partition?by?hash(c2);CREATE?TABLETime:?45.986?ms[local]:5432?pg12@testdb=#?[local]:5432?pg12@testdb=#?\o?/tmp/script.sql[local]:5432?pg12@testdb=#?select?'create?table?t_hash_manypartitions_'pg12@testdb-#???????||idpg12@testdb-#???????||'?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?'||id||');'pg12@testdb-#?from?t_counterpg12@testdb-#?where?id?
插入數(shù)據(jù)
insert?into?t_hash_manypartitions(c1,c2,c3)?values(1,'c2-1','c3-1');
PG 11?
執(zhí)行查詢，條件為c2 = ‘c2-1’
testdb=#?begin;BEGINtestdb=#?explain?analyze?select?*?from?t_hash_manypartitions?where?c2?=?'c2-1';?????????????????????????????????????????????????????????QUERY?PLAN?????????????????????????-----------------------------------------------------------------------------------------------------------------------------?Append??(cost=0.00..14.38?rows=2?width=200)?(actual?time=1.516..1.516?rows=0?loops=1)???->??Seq?Scan?on?t_hash_manypartitions_4956??(cost=0.00..14.38?rows=2?width=200)?(actual?time=1.491..1.491?rows=0?loops=1)?????????Filter:?((c2)::text?=?'c2-1'::text)?Planning?Time:?1585.294?ms?Execution?Time:?2.502?ms(5?rows)
計(jì)劃時(shí)間超過1.5s，比較糟糕的結(jié)果。?
鄭州正規(guī)不孕不育醫(yī)院：http://www.xbzztj.com/
查詢鎖信息
[xdb@localhost?~]$?psql?-d?testdb?-p?5433psql?(11.2)Type?"help"?for?help.testdb=#?select?relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath?testdb-#?from?pg_locks?testdb-#?where?pid?<>?pg_backend_pid();??????????relation??????????|??locktype??|?virtualxid?|?transactionid?|?virtualtransaction?|?pid??|??????mode???????|?granted?|?fastpath?----------------------------+------------+------------+---------------+--------------------+------+-----------------+---------+----------?t_hash_manypartitions_15???|?relation???|????????????|???????????????|?4/2????????????????|?2695?|?AccessShareLock?|?t???????|?t?t_hash_manypartitions_14???|?relation???|????????????|???????????????|?4/2????????????????|?2695?|?AccessShareLock?|?t???????|?t?t_hash_manypartitions_13???|?relation???|????????????|???????????????|?4/2????????????????|?2695?|?AccessShareLock?|?t???????|?t...testdb=#?select?count(*)?from?pg_locks?where?pid?<>?pg_backend_pid();?count?-------??8193(1?row)
PG 12?
執(zhí)行查詢
[local]:5432?pg12@testdb=#?begin;BEGINTime:?0.639?ms[local]:5432?pg12@testdb=#*?explain?analyze?select?*?from?t_hash_manypartitions?where?c2?=?'c2-1';???????????????????????????????????????????????????????QUERY?PLAN???????????????????????????-------------------------------------------------------------------------------------------------------------------------?Seq?Scan?on?t_hash_manypartitions_4956??(cost=0.00..14.38?rows=2?width=200)?(actual?time=22.356..22.356?rows=0?loops=1)???Filter:?((c2)::text?=?'c2-1'::text)?Planning?Time:?75.491?ms?Execution?Time:?22.603?ms(4?rows)Time:?519.835?ms[local]:5432?pg12@testdb=#*
計(jì)劃時(shí)間75ms，比起PG 11的1500ms快了2個(gè)數(shù)量級(jí)。?鄭州不育不孕醫(yī)院：http://www.zzchyy110.com/
查詢鎖信息
[local]:5432?pg12@testdb=#?select?relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath?from?pg_locks?where?pid?<>?pg_backend_pid();??????????relation??????????|??locktype??|?virtualxid?|?transactionid?|?virtualtransaction?|?pid??|??????mode???????|?granted?|?fastpath?----------------------------+------------+------------+---------------+--------------------+------+-----------------+---------+----------?t_hash_manypartitions_4956?|?relation???|????????????|???????????????|?3/4????????????????|?1591?|?AccessShareLock?|?t???????|?t?t_hash_manypartitions??????|?relation???|????????????|???????????????|?3/4????????????????|?1591?|?AccessShareLock?|?t???????|?t????????????????????????????|?virtualxid?|?3/4????????|???????????????|?3/4????????????????|?1591?|?ExclusiveLock???|?t???????|?t(3?rows)Time:?1.935?ms
很好，只是給涉及的分區(qū)上鎖而已。
            
            
                        

            分享名稱：PostgreSQLDBA(-PG12Improv            

            標(biāo)題鏈接：http://weahome.cn/article/iipici.html