Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the PostgreSQL full table scan problem". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand the PostgreSQL full table scan problem".

This section is based on a question from PGer:

Q:

Due to the existence of multiple versions, does a full table scan take longer?

A:

With regard to full table scanning, consider two extreme situations:

1.insert data (transaction committed, same below), no update/delete execution, no dead tuple, no impact on full table scan efficiency

2.insert data, a large number of update/delete is executed, while autovacuum is disabled and vacuum is not executed manually, then there are a large number of dead tuple, one is that more IO operations are required, and the other is that additional CPU judgments are required (visibility judgments are performed for all tuple).

The logic of judgment is as follows:

(Xmin = = my-transaction & & inserted by the current transaction Cmin

= 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

Do a simple experiment and create a table t_fts

1. Insert data, size S1, perform full table scan, time is m seconds

2.update all rows, size S2, perform a full table scan in n seconds.

Theoretically, n should be about s2/s1 times of m (relative to IO time, if the number of tuple is small, CPU time can be ignored).

Create a data table and insert data:

Testdb=# drop table if exists TABLEtestdb=# create table t_fts (id int,c1 varchar), c2 varchar (200), lpad ('c1' | | xmai200), lpad (' c1' | | from generate_series (1Magol 2000000) as xtactInsert 0 2000000testdb=# select pg_size_pretty (pg_table_size ('tactifts')) Pg_size_pretty-868 MB (1 row)

Disable autovacuum and execute the query:

Testdb=# alter system set autovacuum=off;ALTER SYSTEMtestdb=# 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)

Perform update:

Testdb=# update t_fts set C1 = lpad ('c1' | | (id+1), 200 row idlers 1 | |'), c2 = lpad ('c1'| | (id+1), 200 row idlers 1 |'); UPDATE 2000000testdb=# select pg_size_pretty (pg_table_size ('tactifts')); 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) Thank you for reading The above is the content of "how to understand the PostgreSQL full table scan problem". After the study of this article, I believe you have a deeper understanding of how to understand the PostgreSQL full table scan problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.