在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/)

PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)

相關文章