pgsql: Some further performance tweaks for planning large inheritance

Log Message:
-----------
Some further performance tweaks for planning large inheritance trees that
are mostly excluded by constraints: do the CE test a bit earlier to save
some adjust_appendrel_attrs() work on excluded children, and arrange to
use array indexing rather than rt_fetch() to fetch RTEs in the main body
of the planner. The latter is something I'd wanted to do for awhile anyway,
but seeing list_nth_cell() as 35% of the runtime gets one's attention.

Modified Files:
--------------
pgsql/src/backend/optimizer/path:
allpaths.c (r1.162 -> r1.163)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/allpaths.c.diff?r1=1.162&r2=1.163)
clausesel.c (r1.84 -> r1.85)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/clausesel.c.diff?r1=1.84&r2=1.85)
costsize.c (r1.180 -> r1.181)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/costsize.c.diff?r1=1.180&r2=1.181)
pgsql/src/backend/optimizer/plan:
createplan.c (r1.228 -> r1.229)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/createplan.c.diff?r1=1.228&r2=1.229)
planagg.c (r1.30 -> r1.31)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/planagg.c.diff?r1=1.30&r2=1.31)
planmain.c (r1.99 -> r1.100)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/planmain.c.diff?r1=1.99&r2=1.100)
pgsql/src/backend/optimizer/util:
pathnode.c (r1.138 -> r1.139)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/pathnode.c.diff?r1=1.138&r2=1.139)
plancat.c (r1.133 -> r1.134)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/plancat.c.diff?r1=1.133&r2=1.134)
relnode.c (r1.86 -> r1.87)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/relnode.c.diff?r1=1.86&r2=1.87)
pgsql/src/backend/utils/adt:
selfuncs.c (r1.232 -> r1.233)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/selfuncs.c.diff?r1=1.232&r2=1.233)
pgsql/src/include/nodes:
relation.h (r1.140 -> r1.141)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/relation.h.diff?r1=1.140&r2=1.141)

Does this eliminate problems with using a large number of tablespaces?

---------------------------------------------------------------------------

Tom Lane wrote:
> Log Message:
> -----------
> Some further performance tweaks for planning large inheritance trees that
> are mostly excluded by constraints: do the CE test a bit earlier to save
> some adjust_appendrel_attrs() work on excluded children, and arrange to
> use array indexing rather than rt_fetch() to fetch RTEs in the main body
> of the planner. The latter is something I'd wanted to do for awhile anyway,
> but seeing list_nth_cell() as 35% of the runtime gets one's attention.
>
> Modified Files:
> --------------
> pgsql/src/backend/optimizer/path:
> allpaths.c (r1.162 -> r1.163)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/allpaths.c.diff?r1=1.162&r2=1.163)
> clausesel.c (r1.84 -> r1.85)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/clausesel.c.diff?r1=1.84&r2=1.85)
> costsize.c (r1.180 -> r1.181)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/costsize.c.diff?r1=1.180&r2=1.181)
> pgsql/src/backend/optimizer/plan:
> createplan.c (r1.228 -> r1.229)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/createplan.c.diff?r1=1.228&r2=1.229)
> planagg.c (r1.30 -> r1.31)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/planagg.c.diff?r1=1.30&r2=1.31)
> planmain.c (r1.99 -> r1.100)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/planmain.c.diff?r1=1.99&r2=1.100)
> pgsql/src/backend/optimizer/util:
> pathnode.c (r1.138 -> r1.139)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/pathnode.c.diff?r1=1.138&r2=1.139)
> plancat.c (r1.133 -> r1.134)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/plancat.c.diff?r1=1.133&r2=1.134)
> relnode.c (r1.86 -> r1.87)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/relnode.c.diff?r1=1.86&r2=1.87)
> pgsql/src/backend/utils/adt:
> selfuncs.c (r1.232 -> r1.233)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/selfuncs.c.diff?r1=1.232&r2=1.233)
> pgsql/src/include/nodes:
> relation.h (r1.140 -> r1.141)
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/relation.h.diff?r1=1.140&r2=1.141)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Does this eliminate problems with using a large number of tablespaces?

No, it doesn't have anything particular to do with tablespaces.
It gets rid of some bottlenecks that become noticeable with a
large number of child tables.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Does this eliminate problems with using a large number of tablespaces?
>
> No, it doesn't have anything particular to do with tablespaces.
> It gets rid of some bottlenecks that become noticeable with a
> large number of child tables.

Yes, but I thought the problem with many tablespaces was the cost of
referencing them as child tables. Simon?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +