Re: Regression in IN( field, field, field ) performance

Decibel! <decibel(at)decibel(dot)org> writes:
> On Oct 21, 2008, at 12:06 PM, Tom Lane wrote:
>> "Jim 'Decibel!' Nasby" <jnasby(at)cashnetusa(dot)com> writes:
>>> Filter: ('xxxxxxxxxxx'::text = ANY ((ARRAY[home_phone, mobile_phone,
>>> work_phone])::text[]))
>>
> Which means automatic seqscan.
>>
>> It means no such thing.

> It won't use an index scan on this query while it's in that form
> (even with enable_seqscan=off), but if I change it to a bunch of OR'd
> conditions it will switch to bitmap scans.

Works fine for me, eg

regression=# explain select * from tenk1 a, tenk1 b where
regression-# b.unique2 = any(array[a.unique1,a.ten,a.hundred]);
QUERY PLAN

--------------------------------------------------------------------------------
--
Nested Loop (cost=0.79..49047.50 rows=29997 width=488)
-> Seq Scan on tenk1 a (cost=0.00..458.00 rows=10000 width=244)
-> Bitmap Heap Scan on tenk1 b (cost=0.79..4.82 rows=3 width=244)
Recheck Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred]))
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..0.79 rows=3 width=0
)
Index Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred])
)
(6 rows)

You'll need to provide a concrete test case if you think there's
something broken here.

regards, tom lane