From: Atul Deopujari <atuld@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Atul Deopujari <atul.deopujari@enterprisedb.com>
Cc: Neil Conway <neilc@samurai.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Planning large IN lists
Date: Fri, 18 May 2007 00:31:20 +0530
Message-ID: <464CA680.7040904@enterprisedb.com> (raw)
In-Reply-To: <23045.1179408878@sss.pgh.pa.us>
References: <1178821226.6034.63.camel@goldbach>
<19001.1178823208@sss.pgh.pa.us>
<464C1CA0.1060705@enterprisedb.com>
<23045.1179408878@sss.pgh.pa.us>
Hi,
Tom Lane wrote:
> "Atul Deopujari" <atul.deopujari@enterprisedb.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: atuld@enterprisedb.com, tgl@sss.pgh.pa.us, atul.deopujari@enterprisedb.com, neilc@samurai.com
Subject: Re: Planning large IN lists
In-Reply-To: <464CA680.7040904@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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