why does seq scan instead of index scan

From: 中川 誠貴 <nakagawa(at)ivp(dot)co(dot)jp>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: why does seq scan instead of index scan
Date: 2011-01-14 08:58:43
Message-ID: 49A94E2FF0F642B4ACBE09DB20CA5630@IVPNAKAGAWAPC
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2011-01-14 17:56:02 Re: why does seq scan instead of index scan
Previous Message George Francis 2011-01-12 23:38:10 Re: joining one record according to max value