执行计划相关
【参考】
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)
计算逻辑