Postgres Pro
Downloads
Home   >   mailing lists

bitmap heap scan recheck for gin/fts with no lossy blocks - Mailing list pgsql-performance

From Laurent Debacker
Subject bitmap heap scan recheck for gin/fts with no lossy blocks
Date
Msg-id CAG2oYt+yc4bmt128_h7fiTaeLbtfVNFmpP4FH9UA1A5o=RUCOg@mail.gmail.com
Whole thread Raw
Responses Re: bitmap heap scan recheck for gin/fts with no lossy blocks  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
Tree view
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

pgsql-performance by date:

Previous
From: "Graeme B. Bell"
Date:
Subject: parallelisation provides postgres performance (script example + ppt slides)
Next
From: Jeff Janes
Date:
Subject: Re: bitmap heap scan recheck for gin/fts with no lossy blocks