From: 中川 誠貴 <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
Message-ID: <49A94E2FF0F642B4ACBE09DB20CA5630@IVPNAKAGAWAPC> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nakagawa@ivp.co.jp
Subject: Re: why does seq scan instead of index scan
In-Reply-To: <49A94E2FF0F642B4ACBE09DB20CA5630@IVPNAKAGAWAPC>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox