Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Imai Yoshikazu <yoshikazu_i443(at)live(dot)jp>, "jesper(dot)pedersen(at)redhat(dot)com" <jesper(dot)pedersen(at)redhat(dot)com>, "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>, "'Amit Langote'" <amitlangote09(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: speeding up planning with partitions
Date: 2019-03-26 07:16:41
Message-ID: 3340a919-2dec-6f81-2a52-c5428b16b580@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2019/03/25 20:34, Amit Langote wrote:
> Performance loss for smaller number of partitions is in the noise range,
> but what we gain for large number of partitions seems pretty significant
> to me:

I didn't specify the benchmark setup instructions:

partitioned table creation (N: 2...8192):

create table rt (a int, b int, c int) partition by range (a);
select 'create table rt' || x::text || ' partition of rt for values from
(' || (x)::text || ') to (' || (x+1)::text || ');' from generate_series(1,
N) x;
\gexec

select.sql:

\set param random(1, N)
select * from rt where a = :param;

pgbench -n -T 120 -f select.sql

> nparts no live_parts live_parts
> ====== ============= ==========
> 2 3397 3391
> 8 3365 3337
> 32 3316 3379
> 128 3338 3399
> 512 3273 3321
> 1024 3439 3517
> 4096 3113 3227
> 8192 2849 3215
>
> Attached find updated patches.

Rebased patches attached.

Thanks,
Amit

Attachment Content-Type Size
v36-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch text/plain 12.0 KB
v36-0002-Get-rid-of-duplicate-child-RTE-for-partitioned-t.patch text/plain 1.3 KB
v36-0003-Delay-adding-inheritance-child-tables-until-quer.patch text/plain 40.4 KB
v36-0004-Perform-pruning-in-expand_partitioned_rtentry.patch text/plain 28.5 KB
v36-0005-Teach-planner-to-only-process-unpruned-partition.patch text/plain 7.3 KB
v36-0006-Fix-inheritance_planner-to-avoid-useless-work.patch text/plain 6.4 KB
v36-0007-Adjust-inheritance_planner-to-reuse-source-child.patch text/plain 23.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2019-03-26 07:35:07 Re: [HACKERS] WAL logging problem in 9.4.3?
Previous Message Masahiko Sawada 2019-03-26 06:37:07 Re: Re: Copy function for logical replication slots