pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy does seq scan instead of index scan
5+ messages / 4 participants
[nested] [flat]
* why does seq scan instead of index scan
@ 2011-01-14 08:58 =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
0 siblings, 1 reply; 5+ messages in thread
From: $BCf@n!!@?5.(B @ 2011-01-14 08:58 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why does seq scan instead of index scan
@ 2011-01-14 17:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-01-14 17:56 UTC (permalink / raw)
To: $BCf@n!!@?5.(B <nakagawa@ivp.co.jp>; +Cc: pgsql-sql
=?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why does seq scan instead of index scan
@ 2011-01-15 05:19 =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: $BCf@n!!@?5.(B @ 2011-01-15 05:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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: "$BCf@n!!@?5.(B" <nakagawa@ivp.co.jp>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] why does seq scan instead of index scan
> =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why does seq scan instead of index scan
@ 2011-01-15 16:36 Dmitriy Igrishin <dmitigr@gmail.com>
parent: =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Dmitriy Igrishin @ 2011-01-15 16:36 UTC (permalink / raw)
To: 中川 誠貴 <nakagawa@ivp.co.jp>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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
2011/1/15 中川 誠貴 <nakagawa@ivp.co.jp>
> 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
>
>
> =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <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
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
// Dmitriy.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why does seq scan instead of index scan
@ 2011-01-17 01:25 中川 誠貴 <nakagawa@ivp.co.jp>
parent: Dmitriy Igrishin <dmitigr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: 中川 誠貴 @ 2011-01-17 01:25 UTC (permalink / raw)
To: Dmitriy Igrishin <dmitigr@gmail.com>; +Cc: pgsql-sql
Thank you very much.
I will try hstore columns.
Thanks.
From: Dmitriy Igrishin
Sent: Sunday, January 16, 2011 1:36 AM
To: 中川 誠貴
Cc: Tom Lane ; pgsql-sql@postgresql.org
Subject: Re: [SQL] why does seq scan instead of index scan
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
2011/1/15 中川 誠貴 <nakagawa@ivp.co.jp>
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
=?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
// Dmitriy.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-01-17 01:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-14 08:58 why does seq scan instead of index scan =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
2011-01-14 17:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-15 05:19 ` =?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg==?= <nakagawa@ivp.co.jp>
2011-01-15 16:36 ` Dmitriy Igrishin <dmitigr@gmail.com>
2011-01-17 01:25 ` 中川 誠貴 <nakagawa@ivp.co.jp>
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