
In attempting to test the randomness of the random() function (because someone
was complaining on pgsql-general) I found the following strange behaviour.

Shouldn't Postgres be noticing the non-immutable random() function and not
making the subquery an InitPlan? 


 test=> explain select (select * from test order by random() limit 1) as b from b limit 1000;
                                  QUERY PLAN                                 
 ----------------------------------------------------------------------------
  Limit  (cost=72.33..86.74 rows=1000 width=0)
    InitPlan
      ->  Limit  (cost=72.33..72.33 rows=1 width=4)
            ->  Sort  (cost=72.33..74.83 rows=1000 width=4)
                  Sort Key: random()
                  ->  Seq Scan on test  (cost=0.00..22.50 rows=1000 width=4)
    ->  Seq Scan on b  (cost=0.00..2972.00 rows=206300 width=0)
 (7 rows)


I tried putting random() in more places:


 test=> explain select (select random() from test where random() < 0.5 order by random() limit 1) as b from b limit 1000;
                                 QUERY PLAN                                 
 ---------------------------------------------------------------------------
  Limit  (cost=39.84..54.24 rows=1000 width=0)
    InitPlan
      ->  Limit  (cost=39.84..39.84 rows=1 width=0)
            ->  Sort  (cost=39.84..40.67 rows=334 width=0)
                  Sort Key: random()
                  ->  Seq Scan on test  (cost=0.00..25.84 rows=334 width=0)
                        Filter: (random() < 0.5::double precision)
    ->  Seq Scan on b  (cost=0.00..2972.00 rows=206300 width=0)
 (8 rows)



The only way I got it to work properly was by making sure some columns from
the outer table were present in the subquery


 test=> explain select (select case when b.b then a else a end from test order by random() limit 1) as b from b limit 1000;
                                     QUERY PLAN                                    
 ----------------------------------------------------------------------------------
  Limit  (cost=0.00..72345.83 rows=1000 width=1)
    ->  Seq Scan on b  (cost=0.00..14924944.24 rows=206300 width=1)
          SubPlan
            ->  Limit  (cost=72.33..72.33 rows=1 width=4)
                  ->  Sort  (cost=72.33..74.83 rows=1000 width=4)
                        Sort Key: random()
                        ->  Seq Scan on test  (cost=0.00..22.50 rows=1000 width=4)
 (7 rows)



This was with 7.4.3 but I see the same behaviour with a CVS build near 8.0beta2:


 test=# explain select (select * from test order by random() limit 1) as b from b limit 1000;
                                QUERY PLAN                                
 -------------------------------------------------------------------------
  Limit  (cost=1.06..21.06 rows=1000 width=0)
    InitPlan
      ->  Limit  (cost=1.06..1.06 rows=1 width=32)
            ->  Sort  (cost=1.06..1.07 rows=3 width=32)
                  Sort Key: random()
                  ->  Seq Scan on test  (cost=0.00..1.04 rows=3 width=32)
    ->  Seq Scan on b  (cost=0.00..20.00 rows=1000 width=0)
 (7 rows)

 test=# explain select (select random() from test where random() < 0.5 order by random() limit 1) as b from b limit 1000;
                                QUERY PLAN                               
 ------------------------------------------------------------------------
  Limit  (cost=1.06..21.06 rows=1000 width=0)
    InitPlan
      ->  Limit  (cost=1.06..1.06 rows=1 width=0)
            ->  Sort  (cost=1.06..1.06 rows=1 width=0)
                  Sort Key: random()
                  ->  Seq Scan on test  (cost=0.00..1.05 rows=1 width=0)
                        Filter: (random() < 0.5::double precision)
    ->  Seq Scan on b  (cost=0.00..20.00 rows=1000 width=0)
 (8 rows)



-- 
greg


01 Oct 2004 14:47:46 -0400
Greg Stark <gsstark@mit.edu>
Handling of mutable functions in subqueries?
Greg Stark <gsstark@mit.edu> writes:
> Shouldn't Postgres be noticing the non-immutable random() function and not
> making the subquery an InitPlan? 

Perhaps.  In the dim past, using a dummy subquery was the only way to
persuade the planner not to re-evaluate functions that you didn't want
evaluated more than once, so changing this could be expected to break
some existing applications.  Barring some non-invented cases where it
causes problems, I'm inclined to leave it alone.

			regards, tom lane

Fri, 01 Oct 2004 15:09:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Handling of mutable functions in subqueries? 