On 19 April 2018 at 03:13, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Apr 16, 2018 at 10:46 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> I did go and start working on a patch to test how possible this would >> be and came up with the attached. I've left a stray >> MemoryContextStatsDetail call in there which does indicate that >> something is not being freed. I'm just not sure what it is yet. >> >> The patch does happen to improve performance slightly, but that is >> most likely due to the caching of the ExprStates rather than the >> change of memory management. It's not really possible to do that with >> the reset unless we stored the executor's memory context in >> PartitionPruneContext and did a context switch back inside >> partkey_datum_from_expr before calling ExecInitExpr. > > 10% is more than a "slight" improvement, I'd say! It's certainly got > to be worth avoiding the repeated calls to ExecInitExpr, whatever we > do about the memory contexts.
I've attached a patch which does just this. On benchmarking again with this single change performance has improved 15% over master. Also, out of curiosity, I also checked what this performed like before the run-time pruning patch was committed (5c0675215). Taking the average of the times below, it seems without this patch the performance of this case has improved about 356% and about 410% with this patch. So, I agree, it might be worth considering. create table p (a int, value int) partition by hash (a); select 'create table p'||x|| ' partition of p for values with (modulus 10, remainder '||x||');' from generate_series(0,9) x; \gexec create table t1 (a int); insert into p select x,x from generate_Series(1,1000) x; insert into t1 select x from generate_series(1,1000) x; create index on p(a); set enable_hashjoin = 0; set enable_mergejoin = 0; explain analyze select count(*) from t1 inner join p on t1.a=p.a; -- Unpatched Execution Time: 20413.975 ms Execution Time: 20232.050 ms Execution Time: 20229.116 ms -- Patched Execution Time: 17758.111 ms Execution Time: 17645.151 ms Execution Time: 17492.260 ms -- 5c0675215e153ba1297fd494b34af2fdebd645d1 Execution Time: 72875.161 ms Execution Time: 71817.757 ms Execution Time: 72411.730 ms -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
0001-Initialize-expr-states-once-in-run-time-partition-pr.patch
Description: Binary data
