Обсуждение: why does seq scan instead of index scan

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.

中川　誠貴 <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

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 

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




--
// Dmitriy.

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




--
// Dmitriy.