Re: Transforming IN (...) to ORs, volatility - Mailing list pgsql-hackers

Nope, this hasn't been addressed. FWIW, I put it on the todo list when I 
stopped working on it.

On 06.09.2011 20:48, Bruce Momjian wrote:
>
> Uh, have we addressed this?  I don't think so.
>
> ---------------------------------------------------------------------------
>
> Heikki Linnakangas wrote:
>> On 02.04.2011 20:48, Tom Lane wrote:
>>> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>   writes:
>>>> We sometimes transform IN-clauses to a list of ORs:
>>>> postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
>>>>                          QUERY PLAN
>>>> ------------------------------------------------------
>>>>     Seq Scan on foo  (cost=0.00..39.10 rows=19 width=12)
>>>>       Filter: ((a = b) OR (a = c))
>>>> (2 rows)
>>>
>>>> But what if you replace "a" with a volatile function? It doesn't seem
>>>> legal to do that transformation in that case, but we do it:
>>>
>>> This is the fault of transformAExprIn().  But please let's *not* fix
>>> this by adding volatility to the set of heuristics used there.  Looking
>>> at this again, it seems to me that most of the problem with this code
>>> is that we're trying to make optimization decisions in the parser.
>>
>> Agreed. The history of this is that before 8.2 all IN clauses were
>> transformed to OR clauses straight in the grammar. 8.2 added the code to
>> represent IN clause as a ScalarArrayOpExpr, but it was changed in 8.2.10
>> to use the OR-form again for Vars
>> (http://archives.postgresql.org/pgsql-hackers/2008-10/msg01269.php)
>>
>>> I think what we ought to do is have the parser emit a full-fledged
>>> InExpr node type (with semantics rather like CaseExpr) and then teach
>>> the planner to optimize that to something else when it seems
>>> safe/prudent to do so.  One nontrivial advantage of that is that
>>> rules/views containing IN constructs would start to reverse-parse
>>> in the same fashion, instead of introducing weird substitute
>>> expressions.
>>
>> Here's my first cut at that. The lefthand expression is now evaluated
>> only once, and stored in econtext->caseValue. Parse analysis turns the
>> righthand expressions into a list of comparison expressions like
>> "CaseTestExpr = value1". It's perhaps time that we rename CaseTestExpr
>> into something more generic, now that it's used not only in CASE
>> expressions, but also in IN and in UPDATE targets, but I didn't do that
>> in this patch.
>>
>> eval_const_expressions checks the lefthand expression for volatile
>> functions. If there aren't any, it transform the InExprs to a list of ORs.
>>
>> This isn't finished, because it doesn't yet do the transformation to
>> ScalarArrayOpExpr. The OR form is much slower to plan, which is why the
>> ScalarArrayOpExpr transformation was introduced in 8.2. I'll continue
>> hacking on that, but please let me know if you have a better idea on how
>> to handle that. One alternative is to teach the machinery that matches
>> restrictinfos to usable indexes to handle InExpr like it does
>> ScalarArrayOpExprs, but I don't know that code very well.
>>
>> --
>>     Heikki Linnakangas
>>     EnterpriseDB   http://www.enterprisedb.com
>
> [ Attachment, skipping... ]
>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>


--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com