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

On Oct 21, 2008, at 12:06 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby(at)cashnetusa(dot)com> writes:
>> WHERE 'xxxxxxxxxxx' IN (people.home_phone, people.work_phone,
>> people.mobile_phone)
>
>> Yeah, not exactly a common case, but at least in 8.1 this was turned
>> into a set of ORs. Starting in 8.2 and in current HEAD, the planner
>> turns that into:
>
>> 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. The estimated cost with
the seqscans is about 2x more expensive.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828