BUG #15173: why small gin_fuzzy_search_limit search more blocks than big gin_fuzzy_search_limit ?

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: digoal(at)126(dot)com
Subject: BUG #15173: why small gin_fuzzy_search_limit search more blocks than big gin_fuzzy_search_limit ?
Date: 2018-04-25 14:53:23
Message-ID: 152466800389.19805.11513995906384890285@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15173
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 10.3
Operating system: CentOS 7.x x64
Description:

postgres=# set gin_fuzzy_search_limit =10;
SET
Time: 0.188 ms
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*)
from tbl_lab where dict @> array[122562]::int8[] ;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1702903.64..1702903.65 rows=1 width=8) (actual
time=135.104..135.104 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=145266
-> Bitmap Heap Scan on public.tbl_lab (cost=3868.90..1701675.35
rows=491316 width=0) (actual time=135.044..135.082 rows=78 loops=1)
Recheck Cond: (tbl_lab.dict @> '{122562}'::bigint[])
Heap Blocks: exact=78
Buffers: shared hit=145266
-> Bitmap Index Scan on idx_tbl_lab_dict (cost=0.00..3746.07
rows=491316 width=0) (actual time=96.252..96.252 rows=78 loops=1)
Index Cond: (tbl_lab.dict @> '{122562}'::bigint[])
Buffers: shared hit=145248
Planning Time: 0.190 ms
JIT:
Functions: 5
Generation Time: 1.091 ms
Inlining: true
Inlining Time: 5.746 ms
Optimization: true
Optimization Time: 22.590 ms
Emission Time: 10.321 ms
Execution Time: 136.271 ms
(20 rows)

Time: 136.887 ms
postgres=# set gin_fuzzy_search_limit =5000;
SET
Time: 0.222 ms
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*)
from tbl_lab where dict @> array[122562]::int8[] ;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1702903.64..1702903.65 rows=1 width=8) (actual
time=48.953..48.953 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=187
-> Bitmap Heap Scan on public.tbl_lab (cost=3868.90..1701675.35
rows=491316 width=0) (actual time=45.491..48.031 rows=9290 loops=1)
Recheck Cond: (tbl_lab.dict @> '{122562}'::bigint[])
Heap Blocks: exact=9223
Buffers: shared hit=187
-> Bitmap Index Scan on idx_tbl_lab_dict (cost=0.00..3746.07
rows=491316 width=0) (actual time=5.027..5.027 rows=9290 loops=1)
Index Cond: (tbl_lab.dict @> '{122562}'::bigint[])
Buffers: shared hit=166
Planning Time: 0.165 ms
JIT:
Functions: 5
Generation Time: 1.154 ms
Inlining: true
Inlining Time: 6.152 ms
Optimization: true
Optimization Time: 22.501 ms
Emission Time: 10.273 ms
Execution Time: 50.183 ms
(20 rows)

Time: 50.771 ms

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-04-25 17:30:40 BUG #15174: Postgresql 10.3 no response
Previous Message Rémi Aubel 2018-04-25 14:15:36 Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement