Home > mailing lists

Thread: Index scan is not working

Index scan is not working

From

Kiran

Date:

19 September 2016, 15:11:01

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 ?

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

It would be of great help if anyone point me into right direction.

regards

Kiran

Re: Index scan is not working

From

Karsten Hilbert

Date:

19 September 2016, 15:33:31

On Mon, Sep 19, 2016 at 02:10:50PM +0200, Kiran wrote:

> 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 ?

You want PostgreSQL to use an index to try speed up a query
which takes 0.3 milliseconds to select 3 out of 10 rows ?

What is your exact workload that requires speedup of that
query ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Index scan is not working

From

Kiran

Date:

19 September 2016, 15:56:24

Hi Karsten,

Thanks for the response.

I want to know whatever the Analyze output I am getting is normal for a table having few records or something is wrong.

Will the DB engine uses whatever the best way to execute a query irrespective of the indexing in this case?

At the moment the records in the table is very low but that column will be in thousands of rows in the near future. hence I wanted to confirm from the group am I doing something wrong here.

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

regards

Kiran

On Mon, Sep 19, 2016 at 2:33 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

On Mon, Sep 19, 2016 at 02:10:50PM +0200, Kiran wrote:

> 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 ?

You want PostgreSQL to use an index to try speed up a query
which takes 0.3 milliseconds to select 3 out of 10 rows ?

What is your exact workload that requires speedup of that
query ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Index scan is not working

From

Karsten Hilbert

Date:

19 September 2016, 16:27:29

On Mon, Sep 19, 2016 at 02:56:17PM +0200, Kiran wrote:

> I want to know whatever the Analyze output I am getting is normal for a
> table having few records or something is wrong.
> Will the DB engine uses whatever the best way to execute a query
> irrespective of the indexing in this case?

It will use whatever it concluded to seem to be the "best"
way at a given moment, yes.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Index scan is not working

From

Jeff Janes

Date:

19 September 2016, 19:55:22

On Mon, Sep 19, 2016 at 5:10 AM, Kiran <bangalore.kiran@gmail.com> 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