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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
