Thread: regression ? 8.4 do not apply One-Time Filter to subquery
8.4 from CVS HEAD:
EXPLAIN ANALYZE select * from (select n, 1 as r from generate_series(1, 100000) as n union all select n, 2 from
generate_series(1,100000) as n) as x where r = 3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..30.00 rows=10 width=36) (actual time=90.723..90.723 rows=0 loops=1)
-> Append (cost=0.00..30.00 rows=10 width=36) (actual time=90.720..90.720 rows=0 loops=1)
-> Function Scan on generate_series n (cost=0.00..15.00 rows=5 width=36) (actual time=45.191..45.191 rows=0
loops=1)
Filter: (1 = 3)
-> Function Scan on generate_series n (cost=0.00..15.00 rows=5 width=36) (actual time=45.522..45.522 rows=0
loops=1)
Filter: (2 = 3)
Total runtime: 118.709 ms
(7 rows)
8.3.7:
EXPLAIN ANALYZE select * from (select n, 1 as r from generate_series(1, 100000) as n union all select n, 2 from
generate_series(1,100000) as n) as x where r = 3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Result (cost=0.00..25.02 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1)
-> Append (cost=0.00..25.02 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)
-> Result (cost=0.00..12.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
-> Function Scan on generate_series n (cost=0.00..12.50 rows=1 width=4) (never executed)
-> Result (cost=0.00..12.50 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)
One-Time Filter: false
-> Function Scan on generate_series n (cost=0.00..12.50 rows=1 width=4) (never executed)
Total runtime: 0.053 ms
(9 rows)
Is it right ?
--
Sergey Burladyan
On Wed, Jul 1, 2009 at 8:08 PM, Sergey Burladyan<eshkinkot@gmail.com> wrote: > > 8.4 from CVS HEAD: > EXPLAIN ANALYZE select * from (select n, 1 as r from generate_series(1, 100000) as n union all select n, 2 from generate_series(1,100000) as n) as x where r = 3; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------- > Result (cost=0.00..30.00 rows=10 width=36) (actual time=90.723..90.723 rows=0 loops=1) > -> Append (cost=0.00..30.00 rows=10 width=36) (actual time=90.720..90.720 rows=0 loops=1) > -> Function Scan on generate_series n (cost=0.00..15.00 rows=5 width=36) (actual time=45.191..45.191 rows=0 loops=1) > Filter: (1 = 3) > -> Function Scan on generate_series n (cost=0.00..15.00 rows=5 width=36) (actual time=45.522..45.522 rows=0 loops=1) > Filter: (2 = 3) > Total runtime: 118.709 ms > (7 rows) > > 8.3.7: > EXPLAIN ANALYZE select * from (select n, 1 as r from generate_series(1, 100000) as n union all select n, 2 from generate_series(1,100000) as n) as x where r = 3; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------- > Result (cost=0.00..25.02 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1) > -> Append (cost=0.00..25.02 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1) > -> Result (cost=0.00..12.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) > One-Time Filter: false > -> Function Scan on generate_series n (cost=0.00..12.50 rows=1 width=4) (never executed) > -> Result (cost=0.00..12.50 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1) > One-Time Filter: false > -> Function Scan on generate_series n (cost=0.00..12.50 rows=1 width=4) (never executed) > Total runtime: 0.053 ms > (9 rows) > > Is it right ? This might be related to this fix by Tom. http://archives.postgresql.org/message-id/20090711040933.7A08375331E@cvs.postgresql.org ...Robert