Re: Index scan is not working

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Mon, Sep 19, 2016 at 5:10 AM, Kiran <bangalore.kiran@xxxxxxxxx> wrote:

Dear All,

I have a table called question which has a ts_vector column weighted_tsv.

I have gin indexed the weighted_tsv column.

When I query using the following 

EXPLAIN ANALYZE select * from question where weighted_tsv @@ to_tsquery('Hur&ofta');

I get the following output

"Bitmap Heap Scan on question  (cost=12.33..25.38 rows=10 width=731) (actual time=0.058..0.062 rows=3 loops=1)"

"  Recheck Cond: (weighted_tsv @@ to_tsquery('Hur&ofta'::text))"

"  Heap Blocks: exact=3"

"  ->  Bitmap Index Scan on weighted_tsv_question_idx  (cost=0.00..12.33 rows=10 width=0) (actual time=0.052..0.052 rows=3 loops=1)"

"        Index Cond: (weighted_tsv @@ to_tsquery('Hur&ofta'::text))"

"Planning time: 0.205 ms"

"Execution time: 0.104 ms"

Why the query is using the Bitmap ? Not the Index scan ?

Gin indexes do not support non-bitmap index scans, only bitmap index scans.

 

Also to_tsquery is casting to ::text. Is this normal ?

Yes.

It is pointless to test performance against a table with 10 rows.  If your table will soon have a few thousand rows, then either wait until it does, or create a test server and fabricate a few thousand rows worth of test data.

Cheers,

Jeff