在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 width=28) (actual time=122.462..122.462 rows=0 loops=1) Output: id, c1, c2, c3 -> Gather (cost=7755.68..16990.06 rows=30 width=28) (actual time=122.460..123.480 rows=0 loops=1) Output: id, c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.t_bitmapscan (cost=6755.68..15987.06 rows=12 width=28) (actual t ime=119.429..119.429 rows=0 loops=3) Output: id, c1, c2, c3 Recheck Cond: ((t_bitmapscan.c1)::text = '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 width=0) (actual time=88 .728..88.728 rows=300000 loops=1) Index Cond: ((t_bitmapscan.c1)::text = '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 width=28) (actual time=92.754..92.754 rows=0 loops=1) Output: id, c1, c2, c3 -> Gather (cost=1000.42..22522.81 rows=30 width=28) (actual time=92.751..93.677 rows=0 loops=1) Output: id, c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using idx_t_bitmapscan on public.t_bitmapscan (cost=0.42..21519.81 rows=12 width =28) (actual time=81.887..81.887 rows=0 loops=3) Output: id, c1, c2, c3 Index Cond: ((t_bitmapscan.c1)::text = '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](https://bitnine.net/blog-useful-information/bitmap-index-scan/)