From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Should we add GUCs to allow partition pruning to be disabled? |
Date: | 2018-05-01 21:29:35 |
Message-ID: | CAKJS1f-k7+oAfZ5nzK=ivs5dc=AFMsPxiy1shHHZSyLq1PJMbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2 May 2018 at 09:14, David Rowley <david(dot)rowley(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2018-05-01 21:29:48 | Re: A few warnings on Windows |
Previous Message | Miles Elam | 2018-05-01 21:29:29 | Re: Format base - Code contribution |