Re: Partitioning performance: cache stringToNode() of pg_constraint.ccbin

On Tuesday, June 04, 2013 12:37 AM Noah Misch wrote:
> A colleague, Korry Douglas, observed a table partitioning scenario
> where deserializing pg_constraint.ccbin is a hot spot.  The following
> test case, a simplification of a typical partitioning setup, spends 28%
> of its time in
> stringToNode() and callees thereof:
> 
>
> 
> 
> The executor caches each CHECK constraint in ResultRelInfo as a planned
> expression.  That cache is highly effectively for long-running
> statements, but the trivial INSERTs effectively work without a cache.
> Korry devised this patch to cache the stringToNode() form of the
> constraint in the relcache.  It improves the benchmark's partitioned
> scenario by 33%:
> 
> -- Timings (seconds) --
> master, INSERT parent:       14.2, 14.4, 14.4
> patched, INSERT parent:      9.6,  9.7,  9.7
> 
> master, INSERT*10 child:     9.9,  9.9,  10.2
> patched, INSERT*10 child:    10.0, 10.2, 10.2
> 
> There's still not much to like about that tenfold overhead from use of
> the partition routing trigger, but this patch makes a nice cut into
> that overhead without doing anything aggressive.  

This patch can give good performance gain in the scenario described by you.
Infact I had taken the readings with patch, it shows similar gain.

-- Timings (seconds) --
master, INSERT parent:       14.9, 15.4, 15.4
patched, INSERT parent:      9.9,  9.6,  9.5

master, INSERT*10 child:     13.8,  14.5, 15.6
patched, INSERT*10 child:    13.0,  14.3, 14.6

This patch would increase the relcache size, as for each constraint on table
it would increase 4 bytes irrespective of whether that can give performance
benefit or not.
Why in function CheckConstraintFetch(), the node is not formed from string?

> 
> Some call sites need to modify the node tree, so the patch has them do
> copyObject().  I ran a microbenchmark of copyObject() on the cached
> node tree vs. redoing stringToNode(), and copyObject() still won by a
> factor of four.

I have not tried any performance run to measure if extra copyObject() has
added any benefit.
What kind of benchmark you use to validate it?

With Regards,
Amit Kapila.