您好,登錄后才能下訂單哦!
這篇文章主要講解了“怎么理解PostgreSQL全表掃描問(wèn)題”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“怎么理解PostgreSQL全表掃描問(wèn)題”吧!
本節(jié)內(nèi)容來(lái)源于PGer的一個(gè)問(wèn)題:
Q:
由于多版本的存在,那么全表掃描是不是需要更長(zhǎng)的時(shí)間了呢?
A:
關(guān)于全表掃描,不妨考慮2種極端的情況:
1.insert數(shù)據(jù)(事務(wù)已提交,下同),沒(méi)有執(zhí)行update/delete,沒(méi)有dead tuple,全表掃描效率沒(méi)有影響;
2.insert數(shù)據(jù),執(zhí)行了大量的update/delete,同時(shí)禁用了autovacuum也沒(méi)有手工執(zhí)行vacuum,那么存在大量的dead tuple,性能上一是需要更多的IO操作,二是需要執(zhí)行額外的CPU判斷(對(duì)于所有的tuple都要執(zhí)行可見性判斷).
其判斷邏輯如下:
((Xmin == my-transaction && inserted by the current transaction Cmin < my-command && before this command, and (Xmax is null || the row has not been deleted, or (Xmax == my-transaction && it was deleted by the current transaction Cmax >= my-command))) but not before this command, || or (Xmin is committed && the row was inserted by a committed transaction, and (Xmax is null || the row has not been deleted, or (Xmax == my-transaction && the row is being deleted by this transaction Cmax >= my-command) || but it’s not deleted "yet", or (Xmax != my-transaction && the row was deleted by another transaction Xmax is not committed)))) that has not been committed
簡(jiǎn)單做個(gè)實(shí)驗(yàn),創(chuàng)建一張表t_fts,
1.插入數(shù)據(jù),大小為s1,執(zhí)行全表掃描,時(shí)間為m秒;
2.update所有行,大小為s2,執(zhí)行全表掃描,時(shí)間為n秒.
理論上來(lái)說(shuō),n應(yīng)為m的s2/s1倍左右(相對(duì)于IO時(shí)間,如果tuple數(shù)不多,CPU時(shí)間可以忽略不計(jì)).
創(chuàng)建數(shù)據(jù)表,插入數(shù)據(jù):
testdb=# drop table if exists t_fts; DROP TABLE testdb=# create table t_fts(id int,c1 varchar(200),c2 varchar(200)); CREATE TABLE testdb=# testdb=# insert into t_fts select x,lpad('c1'||x,200,'x'),lpad('c1'||x,200,'x') from generate_series(1,2000000) as x; INSERT 0 2000000 testdb=# select pg_size_pretty(pg_table_size('t_fts')); pg_size_pretty ---------------- 868 MB (1 row)
禁用autovacuum,執(zhí)行查詢:
testdb=# alter system set autovacuum=off; ALTER SYSTEM testdb=# show autovacuum; autovacuum ------------ off (1 row) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.t_fts (cost=0.00..131112.16 rows=2000016 width=412) (actual time=0.048..1086.289 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 30.762 ms Execution Time: 1181.360 ms (4 rows)
執(zhí)行update:
testdb=# update t_fts set c1 = lpad('c1'||(id+1),200,id+1||''),c2 = lpad('c1'||(id+1),200,id+1||''); UPDATE 2000000 testdb=# select pg_size_pretty(pg_table_size('t_fts')); pg_size_pretty ---------------- 1737 MB (1 row) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- -- Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=3168.414..6117.780 rows=2000000 loops=1 ) Output: id, c1, c2 Planning Time: 5.493 ms Execution Time: 6205.705 ms (4 rows) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- - Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=776.660..2311.270 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 0.426 ms Execution Time: 2391.895 ms (4 rows) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- - Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=728.758..2293.157 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 0.481 ms Execution Time: 2373.241 ms (4 rows)
感謝各位的閱讀,以上就是“怎么理解PostgreSQL全表掃描問(wèn)題”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)怎么理解PostgreSQL全表掃描問(wèn)題這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。