Re: row estimation off the mark when generate_series calls are involved

Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> wrote:

> I observed the following behavior on PG head:
> postgres=# explain verbose insert into public.x values (generate_series(1,10));
> ------------------------------------------------
> �Insert �(cost=0.00..0.01 rows=1 width=0)

Hmmm, there are differences between "SELECT SRF()" and "SELECT * FROM SRF()":

postgres=# EXPLAIN INSERT INTO public.x SELECT generate_series(1,10);                  QUERY PLAN
------------------------------------------------Insert  (cost=0.00..0.02 rows=1 width=4)  ->  Result  (cost=0.00..0.01
rows=1width=0) 

postgres=# EXPLAIN INSERT INTO public.x SELECT * FROM generate_series(1,10);                                 QUERY PLAN
------------------------------------------------------------------------------Insert  (cost=0.00..10.00 rows=1000
width=4) ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=4) 


> I think the place where we set the
> targetlist of the result_plan to sub_tlist, immediately after that we
> should update the plan_rows estimate by walking this latest
> targetlist. I did that and now we seem to get proper row estimates.

I agree the estimation should be improved, but your patch *adds*
the estimated number of rows to the result:

postgres=# EXPLAIN INSERT INTO public.x SELECT generate_series(1,10);                   QUERY PLAN
---------------------------------------------------Insert  (cost=0.00..12.52 rows=1002 width=4)  ->  Result
(cost=0.00..2.51rows=1001 width=0) 

Should it be 1000 rather than 1001?

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center