Lists: | pgsql-hackers |
---|
From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Handling of mutable functions in subqueries? |
Date: | 2004-10-01 18:47:46 |
Message-ID: | 87r7oi2s31.fsf@stark.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Handling of mutable functions in subqueries? |
Date: | 2004-10-01 19:09:12 |
Message-ID: | 13675.1096657752@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Greg Stark <gsstark(at)mit(dot)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