Обсуждение: bitmap heap scan recheck for gin/fts with no lossy blocks
Hi,
I have read that GIN indexes don't require a recheck cond for full text search as long as work_mem is big enough, otherwise you get lossy blocks, and the recheck cond.
In my case, I have no lossy blocks (from what I could tell), but I do have a recheck...
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(1) FROM enterprises WHERE fts @@ 'activ'::tsquery
"Aggregate (cost=264555.07..264555.08 rows=1 width=0) (actual time=25813.920..25813.921 rows=1 loops=1)"
" Buffers: shared hit=1 read=178192"
" -> Bitmap Heap Scan on enterprises (cost=5004.86..263202.54 rows=541014 width=0) (actual time=170.546..25663.048 rows=528376 loops=1)"
" Recheck Cond: (fts @@ '''activ'''::tsquery)"
" Heap Blocks: exact=178096"
" Buffers: shared hit=1 read=178192"
" -> Bitmap Index Scan on enterprises_fts_idx (cost=0.00..4869.61 rows=541014 width=0) (actual time=120.214..120.214 rows=528376 loops=1)"
" Index Cond: (fts @@ '''activ'''::tsquery)"
" Buffers: shared hit=1 read=96"
"Planning time: 2.383 ms"
"Execution time: 25824.476 ms"
Any advice would be greatly appreciated. I'm running PostgreSQL 9.4.1.
Thank you,
Laurent Debacker
On Thu, Jul 23, 2015 at 9:58 AM, Laurent Debacker <debackerl@gmail.com> wrote:
Hi,I have read that GIN indexes don't require a recheck cond for full text search as long as work_mem is big enough, otherwise you get lossy blocks, and the recheck cond.In my case, I have no lossy blocks (from what I could tell), but I do have a recheck...EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(1) FROM enterprises WHERE fts @@ 'activ'::tsquery"Aggregate (cost=264555.07..264555.08 rows=1 width=0) (actual time=25813.920..25813.921 rows=1 loops=1)"" Buffers: shared hit=1 read=178192"" -> Bitmap Heap Scan on enterprises (cost=5004.86..263202.54 rows=541014 width=0) (actual time=170.546..25663.048 rows=528376 loops=1)"" Recheck Cond: (fts @@ '''activ'''::tsquery)"" Heap Blocks: exact=178096"" Buffers: shared hit=1 read=178192"" -> Bitmap Index Scan on enterprises_fts_idx (cost=0.00..4869.61 rows=541014 width=0) (actual time=120.214..120.214 rows=528376 loops=1)"" Index Cond: (fts @@ '''activ'''::tsquery)"" Buffers: shared hit=1 read=96""Planning time: 2.383 ms""Execution time: 25824.476 ms"Any advice would be greatly appreciated. I'm running PostgreSQL 9.4.1.
The Recheck Cond line is a plan-time piece of info, not a run-time piece. It only tells you what condition is going to be rechecked if a recheck is found to be necessary.
It doesn't indicate how many times it was found it to be necessary to do the recheck. Presumably that number was zero.
Cheers,
Jeff
Thanks Jeff! That makes sense indeed.
I'm a bit surprised a COUNT(1) would need a bitmap heap scan since we know the row count from the index, but okay.
Have a nice day,
Laurent
On Thu, Jul 23, 2015 at 8:04 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, Jul 23, 2015 at 9:58 AM, Laurent Debacker <debackerl@gmail.com> wrote:Hi,I have read that GIN indexes don't require a recheck cond for full text search as long as work_mem is big enough, otherwise you get lossy blocks, and the recheck cond.In my case, I have no lossy blocks (from what I could tell), but I do have a recheck...EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(1) FROM enterprises WHERE fts @@ 'activ'::tsquery"Aggregate (cost=264555.07..264555.08 rows=1 width=0) (actual time=25813.920..25813.921 rows=1 loops=1)"" Buffers: shared hit=1 read=178192"" -> Bitmap Heap Scan on enterprises (cost=5004.86..263202.54 rows=541014 width=0) (actual time=170.546..25663.048 rows=528376 loops=1)"" Recheck Cond: (fts @@ '''activ'''::tsquery)"" Heap Blocks: exact=178096"" Buffers: shared hit=1 read=178192"" -> Bitmap Index Scan on enterprises_fts_idx (cost=0.00..4869.61 rows=541014 width=0) (actual time=120.214..120.214 rows=528376 loops=1)"" Index Cond: (fts @@ '''activ'''::tsquery)"" Buffers: shared hit=1 read=96""Planning time: 2.383 ms""Execution time: 25824.476 ms"Any advice would be greatly appreciated. I'm running PostgreSQL 9.4.1.The Recheck Cond line is a plan-time piece of info, not a run-time piece. It only tells you what condition is going to be rechecked if a recheck is found to be necessary.It doesn't indicate how many times it was found it to be necessary to do the recheck. Presumably that number was zero.Cheers,Jeff
On Fri, Jul 24, 2015 at 2:40 PM, Laurent Debacker <debackerl@gmail.com> wrote:
The Recheck Cond line is a plan-time piece of info, not a run-time piece. It only tells you what condition is going to be rechecked if a recheck is found to be necessary.
Thanks Jeff! That makes sense indeed.I'm a bit surprised a COUNT(1) would need a bitmap heap scan since we know the row count from the index, but okay.
Gin indexes do not (yet) implement index only scans. It has to visit the block to check the visibility of the rows, as visibility data is not stored in the index.
Cheers,
Jeff