Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 18 July 2018 at 21:44, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> part_num | latency_avg | tps_ex | update_latency | select_latency | insert_latency
> ----------+-------------+------------+----------------+----------------+----------------
> 100 | 2.09 | 478.379516 | 1.407 | 0.36 | 0.159
> 200 | 5.871 | 170.322179 | 4.621 | 0.732 | 0.285
> 400 | 39.029 | 25.622384 | 35.542 | 2.273 | 0.758
> 800 | 142.624 | 7.011494 | 135.447 | 5.04 | 1.388
> 1600 | 559.872 | 1.786138 | 534.301 | 20.318 | 3.122
> 3200 | 2161.834 | 0.462574 | 2077.737 | 72.804 | 7.037
> 6400 | 8282.38 | 0.120739 | 7996.212 | 259.406 | 14.514

Thanks for testing. It's fairly customary to include before/after,
unpatched/patched results. I don't think your patched results really
mean much by themselves. It's pretty well known that adding more
partitions slows down the planner and the executor, to a lesser
extent. This patch only aims to reduce some of the executor startup
overheads for INSERT and UPDATE.

Also, the 0001 patch is not really aiming to break any performance
records. I posted results already and there is only a very small
improvement. The main aim with the 0001 patch is to remove the
bottlenecks so that the performance drop between partitioned and
non-partitioned is primarily due to the partition locking. I'd like
to fix that too, but it's more work and I see no reason that we
shouldn't fix up the other slow parts first. I imagine this will
increase the motivation to resolve the locking all partitions issue
too.

I'd also recommend that if you're testing this, that you do so with a
recent master. The patch is not intended for pg11.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services