Thread: why does seq scan instead of index scan

why does seq scan instead of index scan

From
中川 誠貴
Date:
Hello everyone.

I'm trying to use like 'xx%' search on Text[] column.

Here is the SQL.


Select * From table Where 'aa' <= ANY(keywords) and 'aa\uFFFD' > 
ANY(keywords)

This looks like wooking as I intend.

But partially not working.

There is the explanation.

Explain Select * From table Where 'aa' <= ANY(keywords) and 'aa\uFFFD' > 
ANY(keywords)

--\uFFFD is max unicode
-- keywords field is Text[]

"Seq Scan on table  (cost=10000000000.00..10000000081.98 rows=275 
width=1870)"
"  Filter: (('aa'::text <= ANY (keywords)) AND ('aa\uFFFD'::text > ANY 
(keywords)))"


I thought it uses index scan. But actually it uses seq scan.

Why?

I don't get it.


When I seach with the following SQL on Text column, it uses index scan, and 
perfectlly working.

Select * From table Where keyword >= 'aa' and keyword < 'aa\uFFFD'

"Bitmap Heap Scan on table  (cost=4.36..35.63 rows=11 width=1870)"
"  Recheck Cond: ((keyword >= 'aa'::text) AND (keyword < 'aa\uFFFD'::text))"
"  ->  Bitmap Index Scan on table_keyword_idx  (cost=0.00..4.36 rows=11 
width=0)"
"        Index Cond: ((keyword >= 'aa'::text) AND (keyword < 
'aa\uFFFD'::text))"

INDEX table_keywords_idxON tableUSING GIN(keywords);


INDEX table_keyword_idxON tableUSING btree(keyword);


I changed the index, table_keywords_idx to btree, but also not working...


Why index scan is not used on Text[], despite index scan used on Text field?


Thank you in advance.



Re: why does seq scan instead of index scan

From
Tom Lane
Date:
中川 誠貴 <nakagawa@ivp.co.jp> writes:
> I'm trying to use like 'xx%' search on Text[] column.
> I thought it uses index scan. But actually it uses seq scan.

> Why?

Those ANY expressions are not indexable.
        regards, tom lane


Re: why does seq scan instead of index scan

From
中川 誠貴
Date:
Thank you for your response.

Are there any technique to index that query for like search on Text[] 
Columns?


Thanks.


Nakagawa Maskai


--------------------------------------------------
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Saturday, January 15, 2011 2:56 AM
To: "中川 誠貴" <nakagawa@ivp.co.jp>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] why does seq scan instead of index scan

> 中川 誠貴 <nakagawa@ivp.co.jp> writes:
>> I'm trying to use like 'xx%' search on Text[] column.
>> I thought it uses index scan. But actually it uses seq scan.
>
>> Why?
>
> Those ANY expressions are not indexable.
>
> regards, tom lane
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 



Re: why does seq scan instead of index scan

From
Dmitriy Igrishin
Date:
Hey,

To avoid creation of custom GiST-index you can use hstore,
which supports GiST and GIN indexes.
I propose you to add hstore column to the table and write trigger
function which will generate hstore from array on insert or update.
Or you can use hstore column instead of array.
Please, see http://www.postgresql.org/docs/9.0/static/hstore.html

2011/1/15 中川 誠貴 <nakagawa@ivp.co.jp>
Thank you for your response.

Are there any technique to index that query for like search on Text[] Columns?


Thanks.


Nakagawa Maskai


--------------------------------------------------
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Saturday, January 15, 2011 2:56 AM
To: "中川 誠貴" <nakagawa@ivp.co.jp>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] why does seq scan instead of index scan


中川 誠貴 <nakagawa@ivp.co.jp> writes:
I'm trying to use like 'xx%' search on Text[] column.
I thought it uses index scan. But actually it uses seq scan.

Why?

Those ANY expressions are not indexable.

regards, tom lane

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


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



--
// Dmitriy.


Re: why does seq scan instead of index scan

From
中川 誠貴
Date:
Thank you very much.
 
I will try hstore columns.
 
Thanks.
 
 
 

Sent: Sunday, January 16, 2011 1:36 AM
Subject: Re: [SQL] why does seq scan instead of index scan

Hey,

To avoid creation of custom GiST-index you can use hstore,
which supports GiST and GIN indexes.
I propose you to add hstore column to the table and write trigger
function which will generate hstore from array on insert or update.
Or you can use hstore column instead of array.
Please, see http://www.postgresql.org/docs/9.0/static/hstore.html

2011/1/15 中川 誠貴 <nakagawa@ivp.co.jp>
Thank you for your response.

Are there any technique to index that query for like search on Text[] Columns?


Thanks.


Nakagawa Maskai


--------------------------------------------------
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Saturday, January 15, 2011 2:56 AM
To: "中川 誠貴" <nakagawa@ivp.co.jp>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] why does seq scan instead of index scan


中川 誠貴 <nakagawa@ivp.co.jp> writes:
I'm trying to use like 'xx%' search on Text[] column.
I thought it uses index scan. But actually it uses seq scan.

Why?

Those ANY expressions are not indexable.

regards, tom lane

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


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



--
// Dmitriy.