Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: "'Amit Langote'" <amitlangote09(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, "jesper(dot)pedersen(at)redhat(dot)com" <jesper(dot)pedersen(at)redhat(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-08 09:18:00
Message-ID: 43da8422-0259-bda1-8ad5-22f3a4528112@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2019/03/08 16:16, Imai, Yoshikazu wrote:
> So I modified the code and did test to confirm memory increasement don't happen. The test and results are below.
>
> [test]
> * Create partitioned table with 1536 partitions.
> * Execute "update rt set a = random();"
>
> [results]
> A backend uses below amount of memory in update transaction:
>
> HEAD: 807MB
> With v26-0001, 0002: 790MB
> With v26-0001, 0002, 0003: 860MB
> With v26-0003 modified: 790MB

Can you measure with v28, or better attached v29 (about which more below)?

> I attached the diff of modification for v26-0003 patch which also contains some refactoring.
> Please see if it is ok.

I like the is_first_child variable which somewhat improves readability, so
updated the patch to use it.

Maybe you know that range_table_mutator() spends quite a long time if
there are many target children, but I realized there's no need for
range_table_mutator() to copy/mutate child target RTEs. First, there's
nothing to translate in their case. Second, copying them is not necessary
too, because they're not modified across different planning cycles. If we
pass to adjust_appendrel_attrs only the RTEs in the original range table
(that is, before any child target RTEs were added), then
range_table_mutator() has to do significantly less work and allocates lot
less memory than before. I've broken this change into its own patch; see
patch 0004.

Thanks,
Amit

Attachment Content-Type Size
v29-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch text/plain 11.7 KB
v29-0002-Delay-adding-inheritance-child-tables-until-quer.patch text/plain 34.3 KB
v29-0003-Adjust-inheritance_planner-to-reuse-source-child.patch text/plain 22.8 KB
v29-0004-Further-tweak-inheritance_planner-to-avoid-usele.patch text/plain 2.5 KB
v29-0005-Perform-pruning-in-expand_partitioned_rtentry.patch text/plain 29.8 KB
v29-0006-Teach-planner-to-only-process-unpruned-partition.patch text/plain 7.6 KB
v29-0007-Don-t-copy-PartitionBoundInfo-in-set_relation_pa.patch text/plain 1.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2019-03-08 10:07:55 Re: insensitive collations
Previous Message Laurenz Albe 2019-03-08 09:15:21 Re: Small doc fix for pageinspect