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

Nikhil Sontakke <nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:

> I observed the following behavior on PG head:
> postgres=# explain verbose insert into public.x values (generate_series(1,10));
> ------------------------------------------------
> nsert 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=1 width=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.51 rows=1001 width=0)

Should it be 1000 rather than 1001?

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