From: | Laurent Debacker <debackerl(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: bitmap heap scan recheck for gin/fts with no lossy blocks |
Date: | 2015-07-24 21:40:37 |
Message-ID: | CAG2oYt+TbZ73n+QF2ST6egsE=9=7zj257NMZEs7=8UsnrD7GhA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Jul 23, 2015 at 9:58 AM, Laurent Debacker <debackerl(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2015-07-24 22:27:28 | Re: bitmap heap scan recheck for gin/fts with no lossy blocks |
Previous Message | Jeff Janes | 2015-07-23 18:04:16 | Re: bitmap heap scan recheck for gin/fts with no lossy blocks |