执行计划相关

【参考】

https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/

【测试方法】

CREATE TABLE sampletable (x numeric);
INSERT INTO sampletable
SELECT random() * 10000
FROM generate_series(1, 10000000);
CREATE INDEX idx_x ON sampletable(x);

【查单行选择索引扫描】

1)执行sql

test=# explain SELECT * FROM sampletable WHERE x = 42353;
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using idx_x on sampletable (cost=0.43..8.45 rows=1 width=11)
Index Cond: (x = '42353'::numeric)
(2 rows)

2)相关代码

 

 【扫描大量数据选顺序扫描】

postgres=# explain SELECT * FROM sampletable WHERE x < 42353;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on sampletable (cost=0.00..179057.19 rows=10000175 width=11)
Filter: (x < '42353'::numeric)
(2 rows)

计算逻辑

 

 【少量数据选bitmap】

test=# explain SELECT * FROM sampletable WHERE x < 423;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on sampletable (cost=9313.62..68396.35 rows=402218 width=11)
Recheck Cond: (x < '423'::numeric)
-> Bitmap Index Scan on idx_x (cost=0.00..9213.07 rows=402218 width=0)
Index Cond: (x < '423'::numeric)
(4 rows)

计算逻辑

posted @ 2022-02-09 20:32  stupidstan2019  阅读(25)  评论(0编辑  收藏  举报