Another email which went into the wilderness when I sent it to pgsql-patches.
Regards, Nikhils ---------- Forwarded message ---------- From: Nikhil Sontakke <nikhil.sonta...@enterprisedb.com> Date: Fri, Apr 16, 2010 at 6:50 PM Subject: row estimation off the mark when generate_series calls are involved To: pgsql-patc...@postgresql.org Hi, I observed the following behavior on PG head: postgres=# create table x(x int); CREATE TABLE postgres=# explain verbose insert into public.x values (generate_series(1,10)); ------------------------------------------------ Insert (cost=0.00..0.01 rows=1 width=0) postgres=# explain verbose insert into public.x values (generate_series(1,1000)); ------------------------------------------------ Insert (cost=0.00..0.01 rows=1 width=0) So even though generate_series has a prorows value of 1000 (why did we pick this value, just a guesstimate I guess?), its effects are not shown in the plan at all. 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. Comments? Regards, Nikhils -- http://www.enterprisedb.com -- http://www.enterprisedb.com
Index: src/backend/optimizer/plan/planner.c =================================================================== RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/optimizer/plan/planner.c,v retrieving revision 1.267 diff -c -r1.267 planner.c *** src/backend/optimizer/plan/planner.c 30 Mar 2010 21:58:10 -0000 1.267 --- src/backend/optimizer/plan/planner.c 16 Apr 2010 13:46:35 -0000 *************** *** 1241,1246 **** --- 1241,1253 ---- * the desired tlist. */ result_plan->targetlist = sub_tlist; + + /* + * Account for changes in plan row estimates because of + * this tlist addition + */ + result_plan->plan_rows += clamp_row_est( + expression_returns_set_rows((Node *)result_plan->targetlist)); } /*
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
