On 2 May 2018 at 09:14, David Rowley <david.rowley@2ndquadrant.com> wrote:
> It might be worth running a series of benchmarks to test where the worst case performance hit is with partition
pruning.
I just did this:
Setup:
create table parttable (a int, b int) partition by list (a);
create table parttable1 partition of parttable for values in(1);
Benchmark:
$ echo "select * from parttable where b
in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);" >
parttable.sql
$ pgbench -T 30 -f parttable.sql -n postgres
transaction type: parttable.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 230908
latency average = 0.130 ms
tps = 7696.884795 (including connections establishing)
tps = 7697.304782 (excluding connections establishing)
$ psql -c "alter system set enable_partition_pruning = off" postgres
ALTER SYSTEM
$ pgbench -T 30 -f parttable.sql -n postgres
transaction type: parttable.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 233545
latency average = 0.128 ms
tps = 7784.800130 (including connections establishing)
tps = 7785.225490 (excluding connections establishing)
So about 1.1% performance improvement. That's not very much.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services