Skip site navigation (1) Skip section navigation (2)

Re: Transforming IN (...) to ORs, volatility

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Transforming IN (...) to ORs, volatility
Date: 2011-04-02 17:48:03
Message-ID: 27610.1301766483@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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.

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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-04-02 18:53:00
Subject: Re: Another swing at JSON
Previous:From: David FetterDate: 2011-04-02 15:11:37
Subject: Re: [COMMITTERS] pgsql: Escape greater than and less than characters in docs.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group