Re: Speeding up INSERTs and UPDATEs to partitioned tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com>
Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables
Date: 2018-11-16 02:39:56
Message-ID: 20181116023956.pg2ew2nyahip5gnm@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2018-Nov-15, Amit Langote wrote:

> Maybe name it PARTITION_INIT_ALLOCSIZE (dropping the ROUTING from it), or
> PROUTE_INIT_ALLOCSIZE, to make it clear that it's only allocation size?

Here's a proposed delta on v17 0001. Most importantly, I noticed that
the hashed subplans stuff didn't actually work, because the hash API was
not being used correctly. So the search in the hash would never return
a hit, and we'd create RRIs for those partitions again. To fix this, I
added a new struct to hold hash entries.

I think this merits that the performance tests be redone. (Unless I
misunderstand, this shouldn't change the performance of INSERT, only
that of UPDATE.)

On the subject of the ArraySpace routines, I decided to drop them and
instead do the re-allocations on the places where they were needed.
In the original code there were two places for the partitions array, but
both did the same thing so it made sense to create a separate routine to
do it instead (ExecRememberPartitionRel), and do the allocation there.
Just out of custom I moved the palloc to appear at the same place as the
repalloc, and after doing so it became obvious that we were
over-allocating memory for the PartitionDispatchData pointer --
allocating the size for the whole struct instead of just the pointer.

(I renamed the "allocsize" struct members to "max", as is customary.)

I added CHECK_FOR_INTERRUPTS to the ExecFindPartition loop. It
shouldn't be useful if the code is correct, but if there are bugs it's
better to be able to interrupt infinite loops :-)

I reindented the comment atop PartitionTupleRouting. The other way was
just too unwieldy.

Let me know what you think. Regression tests still pass for me.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v18-delta.patch text/x-diff 18.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2018-11-16 03:00:00 Re: WIP: Avoid creation of the free space map for small tables
Previous Message Amit Langote 2018-11-16 01:57:57 Re: ATTACH/DETACH PARTITION CONCURRENTLY