4 ms·
I don't really know what I am talking about, But I thought that postgres function volatility was introduced for this exact reason, that is, As a clue to the opt
by somat 3y ago
I don't really know what I am talking about, But I thought that postgres function volatility was introduced for this exact reason, that is, As a clue to the optimizer where it can and cannot push stuff down.
https://www.postgresql.org/docs/15/xfunc-volatility.html https://www.postgresql.org/docs/15/xfunc-volatility.html
I do note that the postgres explain appears to keep the join filter on the top level.
explain select count(*) from
generate_series(0,999) a
inner join
generate_series(0,999)
on random() < 0.5
;
QUERY PLAN
--------------------------------------------------------------------------------------
Aggregate (cost=25843.34..25843.35 rows=1 width=8)
-> Nested Loop (cost=0.01..25010.01 rows=333333 width=0)
Join Filter: (random() < '0.5'::double precision)
-> Function Scan on generate_series a (cost=0.00..10.00 rows=1000 width=0)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0)
- minkzilla 3y agoAnd if you make a function with with a volatility level of stable it does a One-Time Filter. So you will either get 0 or 1000000 create function random2() returns float as $$ begin return random(); end; $$ language plpgsql stable; select count(*) from one_thousand join one_thousand b on random2() > 0.5; Query Plan Aggregate (cost=97615.13..97615.14 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1) -> Result (cost=0.25..81358.88 rows=6502500 width=0) (actual time=0.026..0.027 rows=0 loops=1) One-Time Filter: (random3() > '0.5'::double precision) -> Nested Loop (cost=0.25..81358.88 rows=6502500 width=0) (never executed) -> Seq Scan on one_thousand (cost=0.00..35.50 rows=2550 width=0) (never executed) -> Materialize (cost=0.00..48.25 rows=2550 width=0) (never executed) -> Seq Scan on one_thousand b (cost=0.00..35.50 rows=2550 width=0) (never executed)