PostgreSQL DBA( - PG 12 Improv

PG 10在分區(qū)表上執(zhí)行查詢時，會逐個檢查每個分區(qū)的約束來看是否需要，如果分區(qū)很多在計劃階段會有較大的性能損失。PG 11通過”partition pruning“算法來快速的標(biāo)識匹配的分區(qū)來改進性能，但PG 11仍然做了一些不必要的處理比如不管是否涉及仍然加載了所有分區(qū)的元數(shù)據(jù)。?
PG 12更進一步，那就是在pruning后才加載元數(shù)據(jù)，如果不涉及大多數(shù)的分區(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?<?8192pg12@testdb-#?order?by?id?;Time:?78.499?ms[local]:5432?pg12@testdb=#?\o[local]:5432?pg12@testdb=#?[root@localhost?~]#?tail?-n?10?/tmp/script.sql??create?table?t_hash_manypartitions_8184?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8184);?create?table?t_hash_manypartitions_8185?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8185);?create?table?t_hash_manypartitions_8186?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8186);?create?table?t_hash_manypartitions_8187?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8187);?create?table?t_hash_manypartitions_8188?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8188);?create?table?t_hash_manypartitions_8189?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8189);?create?table?t_hash_manypartitions_8190?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8190);?create?table?t_hash_manypartitions_8191?partition?of?t_hash_manypartitions?for?values?with?(modulus?8192,remainder?8191);(8192?rows)[local]:5432?pg12@testdb=#?\i?/tmp/script.sql...CREATE?TABLETime:?20.784?msCREATE?TABLETime:?21.107?mspsql:/tmp/script.sql:8196:?ERROR:??syntax?error?at?or?near?"8192"LINE?1:?(8192?rows)?????????^Time:?0.198?ms[local]:5432?pg12@testdb=#

插入數(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)

計劃時間超過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=#*

計劃時間75ms，比起PG 11的1500ms快了2個數(shù)量級。?鄭州不育不孕醫(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ū)上鎖而已。