Re: Bad performance of SELECT ... where id IN (...)

Robert,

On Mon, Oct 5, 2009 at 11:01 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Oct 4, 2009 at 9:58 PM, Omar Kilani <omar(dot)kilani(at)gmail(dot)com> wrote:
>> Hi Xia,
>>
>> Try this patch:
>>
>> http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch
>>
>> It's a hack, but it works for us. I think you're probably spending
>> most of your query time planning, and this patch helps speed things up
>> 10x over here.
>
> Woof.  I can see that helping in some situations, but what a foot-gun!

We've run that patch for about 4 years (originally coded for us by
Neil Conway for 8.2, I think), and have never seen any negatives from
it.

I'm not really sure what the alternatives are -- it never really makes
sense to get the selectivity for thousands of items in the IN clause.
I've never seen a different plan for the same query against a DB with
that patch vs without -- it just takes a huge amount of time longer to
run without it. :)

But yeah, definitely a hack, and should only be used if needed --
hopefully there's some sort of official solution on the horizon. :)

> ...Robert

Regards,
Omar