BUG #14129: Why GIN index not use index scan?
От | digoal@126.com |
---|---|
Тема | BUG #14129: Why GIN index not use index scan? |
Дата | |
Msg-id | 20160507145302.2680.33288@wrigleys.postgresql.org обсуждение исходный текст |
Список | pgsql-bugs |
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)
В списке pgsql-bugs по дате отправления: