RE: Small performance tweak to run-time partition pruning - Mailing list pgsql-hackers

Sorry for the delay in replying.

On Wed, Oct 10, 2018 at 4:05 PM, David Rowley wrote:
> > It seems to me that there is no problem in this patch as far.
> > Is there another thing I have to do for the review?
> 
> There's a checklist in [1]. Perhaps there's something mentioned there
> that you've missed.
> 
> [1] https://wiki.postgresql.org/wiki/Reviewing_a_Patch

Thanks for the URL.


I did the performance test which is almost same as you did but only changed
"\set p_id 1" in select.sql for another performance test that I will send
that result in next mail. Maybe it doesn't affect the performance, so it's ok.

I tested with master(28d750c) + v2patch.

[Unpatched]
ave 3669 TPS

tps = 3700.319242 (excluding connections establishing)
tps = 3642.287089 (excluding connections establishing)
tps = 3668.243399 (excluding connections establishing)
tps = 3689.457722 (excluding connections establishing)
tps = 3714.309178 (excluding connections establishing)
tps = 3697.488958 (excluding connections establishing)
tps = 3573.372327 (excluding connections establishing)
tps = 3620.473191 (excluding connections establishing)
tps = 3689.794860 (excluding connections establishing)
tps = 3692.317099 (excluding connections establishing)

[Patched]
ave 3718 TPS

tps = 3751.639616 (excluding connections establishing)
tps = 3736.482071 (excluding connections establishing)
tps = 3747.613223 (excluding connections establishing)
tps = 3745.578446 (excluding connections establishing)
tps = 3662.612013 (excluding connections establishing)
tps = 3715.271028 (excluding connections establishing)
tps = 3718.671552 (excluding connections establishing)
tps = 3698.766946 (excluding connections establishing)
tps = 3639.026099 (excluding connections establishing)
tps = 3760.507508 (excluding connections establishing)

The patch improves the performance about 1.3% which is less than David's
result, but it seems still improves the performance.


Above performance test don't exec run-time pruning and we can't check the 
performance of the loop codes patch modified, so I also did the below test
which do exec run-time pruning.

setup:
CREATE TABLE partbench (id BIGINT NOT NULL, i1 INT NOT NULL, i2 INT
NOT NULL, i3 INT NOT NULL, i4 INT NOT NULL, i5 INT NOT NULL) PARTITION
BY RANGE (id);

\o /dev/null
select 'CREATE TABLE partbench' || x::text || ' PARTITION OF partbench
FOR VALUES FROM (' || (x*100000)::text || ') TO (' ||
((x+1)*100000)::text || ') PARTITION BY RANGE (i1);'
from generate_Series(0,299) x;
\gexec
\o

\o /dev/null
select 'CREATE TABLE partbench' || x::text ||
'_i1a PARTITION OF partbench' || x::text ||
' FOR VALUES FROM (0) TO (1);' from generate_Series(0,299) x;
\gexec
\o

\o /dev/null
select 'CREATE TABLE partbench' || x::text ||
'_i1b PARTITION OF partbench' || x::text ||
' FOR VALUES FROM (1) TO (2);' from generate_Series(0,299) x;
\gexec
\o


select-exec-init.sql:
\set p_id 1
select * from partbench where id = :p_id and i1 = (select 0);


[Unpatched]
ave  1060 TPS

tps = 1067.286500 (excluding connections establishing)
tps = 1052.705136 (excluding connections establishing)
tps = 1056.684966 (excluding connections establishing)
tps = 1059.803865 (excluding connections establishing)
tps = 1053.418776 (excluding connections establishing)
tps = 1053.383518 (excluding connections establishing)
tps = 1058.542617 (excluding connections establishing)
tps = 1071.875455 (excluding connections establishing)
tps = 1058.064092 (excluding connections establishing)
tps = 1066.869393 (excluding connections establishing)

[Patched]
ave 1069 TPS

tps = 1071.621247 (excluding connections establishing)
tps = 1067.881709 (excluding connections establishing)
tps = 1073.274357 (excluding connections establishing)
tps = 1058.648528 (excluding connections establishing)
tps = 1068.490598 (excluding connections establishing)
tps = 1064.739885 (excluding connections establishing)
tps = 1069.189778 (excluding connections establishing)
tps = 1070.253092 (excluding connections establishing)
tps = 1070.395411 (excluding connections establishing)
tps = 1071.003647 (excluding connections establishing)

The patch also improves the performance about 0.85% in this case.

--
Yoshikazu Imai