PostgreSQLDBA(119)pgAdmin(LIMIT：IndexScanvsBitmapIndexScan)

在PostgreSQL中，如使用limit，但执行计划中使用了Bitmap Index Scan，则需要引起注意，可能PG计算成本时认为Bitmap Index Scan比Index Scan或者Seq Scan要低，但实际上可能时间要比其他方式要长，原因是Bitmap Index Scan需要完成Index Rows的扫描在内存中建立Bitmap后才能访问表数据，而Index Scan或者Seq Scan则没有预先步骤直接扫描一行输出一行，因此Bitmap Index Scan在存在limit子句时性能可能会较差，尤其是在系统负载比较大的情况下。

Index Scan vs Bitmap Index Scan

Index Scan

Index scan reads the index in alternation, bouncing between table and index, row at a time.
Random I/O against the base table. Read a row from the index, then a row from the table, and so on.
useful in combination with LIMIT

Bitmap Index Scan

Scans all index rows before examining base table.This populates a TID bitmap.
Table I/O is sequential, results in physical order.
Handles LIMIT poorly

[local]:5432 pg12@testdb=# create table t_bitmapscan(id int,c1 varchar,c2 varchar,c3 varchar);
CREATE TABLE
Time: 20.424 ms
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# insert into t_bitmapscan(id,c1,c2,c3)
pg12@testdb-# select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,700000) as x;
INSERT 0 700000
Time: 1695.539 ms (00:01.696)
[local]:5432 pg12@testdb=# insert into t_bitmapscan(id,c1,c2,c3)
pg12@testdb-# select x,'c1'||700001,'c2'||x,'c3'||x from generate_series(700001,1000000) as x;
INSERT 0 300000
Time: 712.709 ms
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# create index idx_t_bitmapscan on t_bitmapscan(c1);
CREATE INDEX
Time: 1002.897 ms (00:01.003)
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# explain (analyze,verbose) select * from t_bitmapscan where c1 = 'c1700001' and c2 like 'c2600%' limit 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=7755.68..13911.93 rows=20 c1700001'::text)
Filter: ((t_bitmapscan.c2)::text ~~ 'c2600%'::text)
Rows Removed by Filter: 100000
Heap Blocks: exact=939
Worker 0: actual time=118.092..118.093 rows=0 loops=1
Worker 1: actual time=118.503..118.503 rows=0 loops=1
-> Bitmap Index Scan on idx_t_bitmapscan (cost=0.00..6755.68 rows=300700 c1700001'::text)
Planning Time: 0.596 ms
Execution Time: 123.540 ms -->实际执行时间是123ms，计划成本较低
(18 rows)
Time: 124.760 ms
[local]:5432 pg12@testdb=# set enable_bitmapscan=off;
SET
Time: 1.378 ms
[local]:5432 pg12@testdb=# explain (analyze,verbose) select * from t_bitmapscan where c1 = 'c1700001' and c2 like 'c2600%' limit 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=1000.42..15348.68 rows=20 c1700001'::text)
Filter: ((t_bitmapscan.c2)::text ~~ 'c2600%'::text)
Rows Removed by Filter: 100000
Worker 0: actual time=76.905..76.905 rows=0 loops=1
Worker 1: actual time=77.297..77.297 rows=0 loops=1
Planning Time: 2.448 ms
Execution Time: 93.785 ms --> 实际执行时间为93ms，但计划成本较高
(15 rows)
Time: 97.455 ms
[local]:5432 pg12@testdb=#


参考资料
Bitmap Index Scan