Thread: planner's midjudge number of rows resulting, despite pretty obvious join
planner's midjudge number of rows resulting, despite pretty obvious join
From
Grzegorz Jaśkiewicz
Date:
explain select ss, ARRAY(select id from foo where ss>0 and id between
7 and 156 order by random() limit 3) as v from
generate_series(1,1000000) ss;
QUERY PLAN
------------------------------------------------------------------------------------
Function Scan on generate_series ss (cost=0.00..9381.22 rows=1000 width=4)
SubPlan
-> Limit (cost=9.36..9.37 rows=3 width=8)
-> Sort (cost=9.36..9.74 rows=150 width=8)
Sort Key: (random())
-> Result (cost=0.00..7.42 rows=150 width=8)
One-Time Filter: ($0 > 0)
-> Seq Scan on foo (cost=0.00..7.05 rows=150 width=8)
Filter: ((id >= 7) AND (id <= 156))
(9 rows)
:(
no matter if I change last generate_series's range, it will always
estimate 1000 rows...
--
GJ
Re: planner's midjudge number of rows resulting, despite pretty obvious join
From
Pavel Stehule
Date:
Hello
2009/2/24 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> explain select ss, ARRAY(select id from foo where ss>0 and id between
> 7 and 156 order by random() limit 3) as v from
> generate_series(1,1000000) ss;
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Function Scan on generate_series ss (cost=0.00..9381.22 rows=1000 width=4)
> SubPlan
> -> Limit (cost=9.36..9.37 rows=3 width=8)
> -> Sort (cost=9.36..9.74 rows=150 width=8)
> Sort Key: (random())
> -> Result (cost=0.00..7.42 rows=150 width=8)
> One-Time Filter: ($0 > 0)
> -> Seq Scan on foo (cost=0.00..7.05 rows=150 width=8)
> Filter: ((id >= 7) AND (id <= 156))
> (9 rows)
>
> :(
>
> no matter if I change last generate_series's range, it will always
> estimate 1000 rows...
>
>
There are not dynamic estimator for SRF function. You can change it
statically via ROWS flag - default is 1000 rows.
postgres=# create or replace function fooo() returns setof int as $$
select * from (values(10),(20)) x$$ language sql;
CREATE FUNCTION
postgres=# explain select * from fooo();
QUERY PLAN
--------------------------------------------------------------
Function Scan on fooo (cost=0.00..260.00 rows=1000 width=4)
(1 row)
postgres=# create or replace function fooo() returns setof int as $$
select * from (values(10),(20)) x$$ language sql rows 432;
CREATE FUNCTION
postgres=# explain select * from fooo();
QUERY PLAN
-------------------------------------------------------------
Function Scan on fooo (cost=0.00..112.32 rows=432 width=4)
(1 row)
postgres=#
regards
Pavel Stehule
> --
> GJ
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>