Message-ID: <49A94E2FF0F642B4ACBE09DB20CA5630@IVPNAKAGAWAPC>
From: =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
To: <pgsql-sql@postgresql.org>
Subject: why does seq scan instead of index scan
Date: Fri, 14 Jan 2011 17:58:43 +0900
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-2022-jp";
	reply-type=original
Content-Transfer-Encoding: 7bit
Importance: Normal

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_idx
	ON table
	USING GIN
	(keywords);


INDEX table_keyword_idx
	ON table
	USING 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.