Re: Planning large IN lists

Hi,

Tom Lane wrote:
> "Atul Deopujari" <atul(dot)deopujari(at)enterprisedb(dot)com> writes:
>> Hi,
>> Tom Lane wrote:
>>> That's the least of the problems. We really ought to convert such cases
>>> into an IN (VALUES(...)) type of query, since often repeated indexscans
>>> aren't the best implementation.
>>>
>> I thought of giving this a shot and while I was working on it, it
>> occurred to me that we need to decide on a threshold value of the IN
>> list size above which such transformation should take place.
>
> I see no good reason to suppose that there is/should be a constant
> threshold --- most likely it depends on size of table, availability of
> indexes, etc. Having the planner try it both ways and compare costs
> would be best.
>
Yes, letting the planner make its own decision would seem best (in
accordance with what we do for different join paths). But for large IN
lists, a substantial part of the planner is spent in estimating the
selectivity of the ScalarArrayExpr by calling scalararraysel. If we are
not eliminating this step in processing the IN list then we are not
doing any optimization. Asking the planner to do scalararraysel and also
compute cost of any other way and choose between the two is asking
planner to do more work.

Factors such as size of table, availability of index etc. would affect
both the ways similarly. So, if we see a gain in the execution of the IN
list due to an external factor then we will also see a similar gain in
the execution of the transformed IN (VALUES(...)) clause.

I agree that one value would not fit all cases. The problem with this
approach is that for some cases, large IN list would perform better than
the transformed IN (VALUES(...)) clause. But we know that the
transformed IN (VALUES(...)) clause has almost a steady state behavior
and it would not blow off the planner estimates. The error would be just
marginal.

--
Atul

EnterpriseDB
www.enterprisedb.com