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

On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> 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))
>
> 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:
>
> postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c);
>                                                     QUERY PLAN
>
>  Seq Scan on foo  (cost=0.00..68.20 rows=19 width=12)
>   Filter: ((((random() * 2::double precision))::integer = b) OR (((random()
> * 2::double precision))::integer = c))

Is there a similar problem with the BETWEEN clause transformation into
AND expressions?

marti=> explain verbose select random() between 0.25 and 0.75;Result  (cost=0.00..0.02 rows=1 width=0)  Output:
((random()>= 0.25::double precision) AND (random() <= 
0.75::double precision))

As expected, I get a statistical skew of 0.4375 / 0.5625, whereas the
"correct" would be 50/50:

marti=> select random() between 0.25 and 0.75 as result, count(*) from
generate_series(1,1000000) i group by 1;result | count
--------+--------f      | 437262t      | 562738

I also always noticed that BETWEEN with subqueries produces two
subplan nodes, this seems suboptimal.

marti=> explain verbose select (select random()) between 0.25 and 0.75;Result  (cost=0.03..0.04 rows=1 width=0)
Output:(($0 >= 0.25::double precision) AND ($1 <= 0.75::double precision))  InitPlan 1 (returns $0)    ->  Result
(cost=0.00..0.01rows=1 width=0)          Output: random()  InitPlan 2 (returns $1)    ->  Result  (cost=0.00..0.01
rows=1width=0)          Output: random() 


Regards,
Marti