On 2 May 2018 at 09:14, David Rowley <david.row...@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
