The following bug has been logged on the website:
Bug reference: 14129
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.5.2
Operating system: CentOS 6.x x64
Description:
In some case , people will use limit get data, or use cursor get data.
bitmap scan not very good for these use case, because it must sort all
matched ctid first.
GIN index implement bitmap index scan only?
why not let's gin index can use index scan?
This is test case:
```
postgres=# create table t3(id int, info int[]);
CREATE TABLE
postgres=# insert into t3 select generate_series(1,10000),array[1,2,3,4,5];
INSERT 0 10000
postgres=# create index idx_t3_info on t3 using gin(info);
CREATE INDEX
postgres=# set enable_seqscan=off;
SET
```
get all data it's good.
postgres=# explain analyze select * from t3 where info && array [1] ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t3 (cost=83.00..302.00 rows=10000 width=45) (actual
time=1.156..3.565 rows=10000 loops=1)
Recheck Cond: (info && '{1}'::integer[])
Heap Blocks: exact=94
-> Bitmap Index Scan on idx_t3_info (cost=0.00..80.50 rows=10000
width=0) (actual time=1.129..1.129 rows=10000 loops=1)
Index Cond: (info && '{1}'::integer[])
Planning time: 0.107 ms
Execution time: 5.272 ms
(7 rows)
but get small data , it's not good. ctid sort waste resource.
postgres=# explain analyze select * from t3 where info && array [1] limit
1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=83.00..83.02 rows=1 width=45) (actual time=1.121..1.121 rows=1
loops=1)
-> Bitmap Heap Scan on t3 (cost=83.00..302.00 rows=10000 width=45)
(actual time=1.119..1.119 rows=1 loops=1)
Recheck Cond: (info && '{1}'::integer[])
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_t3_info (cost=0.00..80.50 rows=10000
width=0) (actual time=1.095..1.095 rows=10000 loops=1)
Index Cond: (info && '{1}'::integer[])
Planning time: 0.113 ms
Execution time: 1.175 ms
(8 rows)