CTID是什么

CTID是一个系统列,用于标识某一元组位于哪个位置,由(block number + 块内的偏移量offset)组成和oracle的rowid十分类似

select attname from pg_attribute where attrelid='test2'::regclass;
attname
----------
cmax
cmin
ctid 类似oracle的row_id
id
info
tableoid 分区表可以通过该值快速找到子表
xmax
xmin
(8 rows)

例如

postgres=# select * ,ctid from test2 limit 10;
id | info | ctid
----+------+--------
1 | test | (0,1) 第0个数据库第一行
2 | test | (0,2)
3 | test | (0,3)
4 | test | (0,4)
5 | test | (0,5)
6 | test | (0,6)
7 | test | (0,7)
8 | test | (0,8)
9 | test | (0,9)
10 | test | (0,10)
(10 rows)


PG扫描方式类型

  1. Sequential Scan
  2. Index Scan (tid回表)
  3. Index Only Scan (直接从索引里面区全数据)相当于mysql的覆盖索引
  4. Bitmap Scan
  5. TID Scan (比如select * from test where ctid ='(0,5)';)

每一个扫描方法都同样有用,这取决于查询本身,和一些其他的参数,如表的基数、表的选择率、磁盘随机IO的成本、顺序IO的成本等等。让我们先创建一些表,并填充一些数据,这会方便解释这些扫描方法。

postgres=# drop table demotable;
DROP TABLE
postgres=# CREATE TABLE demotable (num numeric, id int);
CREATE TABLE
postgres=# CREATE INDEX demoidx ON demotable(num);
CREATE INDEX
postgres=# INSERT INTO demotable SELECT random() * 1000, generate_series(1, 1000000);
INSERT 0 1000000
postgres=# analyze;
ANALYZE

在这个例子,插入了1千万数据,并且进行了analyze,以确保所有的统计信息是最新的。


顺序扫描

顾名思义,对某表的顺序扫描是通过顺序扫描对应表所有页面的所有项目指针来完成的。因此,如果某个表有100页,然后每页中有1000条记录,那么作为顺序扫描的一部分,它会获取100 * 1000条记录,并根据隔离级别和谓词子句检查是否匹配。因此,即使在整个表扫描中只选择了1条记录,也必须扫描100K条记录才能根据条件找到合格的记录。

根据上面的表和数据,由于选择了大多数数据,因此以下查询将导致顺序扫描。

postgres=#  explain SELECT * FROM demotable WHERE num < 21000; 
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on demotable (cost=0.00..17990.00 rows=1000000 width=15)
Filter: (num < '21000'::numeric)
(2 rows)

成本是什么算的

cost表示启动成本

rows表示预估返回行数

width表示返回每行的平均字节

postgres=# select name ,setting from pg_settings where name like '%cost%';
name | setting
------------------------------+---------
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
cpu_index_tuple_cost | 0.005 cpu处理一个索引的成本
cpu_operator_cost | 0.0025 cpu处理一个操作的成本
cpu_tuple_cost | 0.01 cpu处理一行数据的成本
jit_above_cost | 100000
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
parallel_setup_cost | 1000 初始化成本
parallel_tuple_cost | 0.1
random_page_cost | 4 随机扫描一个page的成本是4
seq_page_cost | 1 顺序扫描一个page的成本是1
vacuum_cost_delay | 0
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
(17 rows)

cost成本计算方式

postgres=#  explain SELECT * FROM demotable WHERE num < 21000; 
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on demotable (cost=0.00..17990.00 rows=1000000 width=15)

postgres=# select reltuples ,relpages from pg_class where relname='demotable';
reltuples | relpages
-----------+----------
1e+06 | 5490


cost计算方式
seq_page_cost*relpages+cpu_tuple_cost*rows+cpu_operator_cost*rows

postgres=# select 5490*1+0.01*1000000+0.0025*1000000;
?column?
------------
17990.0000
(1 row)


索引扫描

索引由 key(索引列) +CTID 组成

从索引相关数据结构中取数据,返回数据的TID根据TID回表获得整个数据。

索引扫描有两个步骤:

1. 从索引的相关数据结构中获取数据。它返回Heap中相应数据的TID。

2. 然后直接访问相应的Heap页以获得整个数据。由于以下原因,需要这一额外步骤:

1)查询请求的列数量可能超过了索引中的列数量。

2)可见性信息不会和索引一起维护。因此,为了根据隔离级别检查数据的可见性,仍需要访问堆内数据。

现在我们可能会疑惑,既然索引如此高效,为何不总是使用索引扫描。正如我们所知,每件事都是有代价的。这里涉及的成本与我们正在执行的IO类型有关。对于索引扫描,涉及到随机IO,就像在索引中找到的每个记录一样,它必须从HEAP存储中获取相应的数据,而对于顺序扫描,则涉及到顺序IO,顺序IO大约只需要花费25% 随机IO的时间。因此,由于有随机IO的存在,只有整体的增益超过开销时,才应选择索引扫描。

postgres=#  explain SELECT * FROM demotable WHERE num = 21000;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=15)
Index Cond: (num = '21000'::numeric)
(2 rows)

postgres=#
postgres=#
postgres=# explain select num from demotable where num =21000;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=11)
Index Cond: (num = '21000'::numeric)
(2 rows)

仅索引扫描

类似于索引扫描,除了第二步,顾名思义,它只扫描索引数据结构。为了选择仅索引扫描而不是索引扫描,还有两个附加的前提条件:

  • 查询只获取索引中所包含的列。
  • 对应堆页上的所有元组(记录)都应该可见。索引数据结构并不会维护可见性信息,因此,为了仅从索引中获取数据,我们应避免检查可见性,如果认为该页面上的所有数据都认为是可见的,则可能会发生这种情况。
  • 上面的例子的查询将使用仅扫描索引。即使这个查询在选择记录的数量方面几乎和前一节的索引扫描是类似的,但因为只有字段(即num)被选择,所以它会选择Index only Scan。
  • 索引里面并不存放可见性信息,VM可以告诉pg某个数据块的数据是可见的
  • 就是没有死元组,那么我们可以直接将索引里面的值返回,不需要一次额外的IO操作。可能还是会回表,更新后,数据块显示not_all_visible,则会回表查询数据块去检验可见性。
postgres=# explain analyze select num from demotable where num =21000;
QUERY PLAN

------------------------------------------------------------------------------------------------
-------------------------
Index Only Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=11) (actual time=0.04
9..0.049 rows=0 loops=1)
Index Cond: (num = '21000'::numeric)
Heap Fetches: 0 回表次数
Planning Time: 0.061 ms
Execution Time: 0.063 ms
(5 rows)

位图扫描

消除离散,重复读HEAP

是索引扫描和顺序扫描的结合,试图解决索引扫描的缺点,索引扫描每一条都需要在Heap页中找到对应的数据,因此,它还需要先获取索引页,再获取堆页,导致大量的随机IO。

  • Bitmap Index Scan:首先,它从索引数据结构中获取所有索引数据,并创建所有TID的位图映射。为了简单理解,可以将此位图看作包含所有页面的散列(基于页号的散列),并且每个页面条目包含一个包含该页的页内所有偏移量的数组。
  • Bitmap Heap Scan:顾名思义,它通过读取页面的位图,然后从堆中扫描对应的页和偏移量。最后,它检查可见性和谓词等,并根据所有这些检查的结果返回元组。

​位图扫描利用了索引扫描的优势,而无需使用随机I / O

位图扫描方式是bitmap index scan -->位图比对-->内存排序-->回表。

postgres=# explain SELECT * FROM demotable WHERE num < 210;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on demotable (cost=5784.40..13900.16 rows=210061 width=15)
Recheck Cond: (num < '210'::numeric)
-> Bitmap Index Scan on demoidx (cost=0.00..5731.88 rows=210061 width=0)
Index Cond: (num < '210'::numeric)
(4 rows)

考虑下面的查询,该查询获取相同数量的记录,但仅选择关键字段(即仅索引列)。由于它仅获取索引列,因此不需要为其他部分数据而去引用堆页面,因此不会涉及到随机I / O。因此,此查询将选择“仅索引扫描”而不是“位图扫描”。

postgres=# explain SELECT num FROM demotable WHERE num < 210;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Only Scan using demoidx on demotable (cost=0.42..7784.87 rows=208254 width=11)
Index Cond: (num < '210'::numeric)
(2 rows)

TID扫描

查找谓词中有TID

postgres=# explain select * from demotable where ctid='(0,10)';
QUERY PLAN
----------------------------------------------------------
Tid Scan on demotable (cost=0.00..4.01 rows=1 width=15)
TID Cond: (ctid = '(0,10)'::tid)
(2 rows)