Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 6 July 2018 at 01:18, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> wrote:
> With 0002 INSERTs get close to the TPS of the non-partitioned case. However,
> UPDATEs doesn't see the same speedup. But, as you said, a discussion for
> another thread.

Hi Jesper,

Thanks for testing this out. It was only really the locking I didn't
want to discuss here due to the risk of the discussion of removing the
other overheads getting lost in discussions about locking.

It's most likely that the UPDATE is slower due to the planner still
being quite slow when dealing with partitioned tables. It still builds
RangeTblEntry and RelOptInfo objects for each partition even if the
partition is pruned. With INSERT with a VALUES clause, the planner
does not build these objects, in fact, the planner bearly does any
work at all, so this can be speeded up just by removing the executor
overheads.

(I do have other WIP patches to speed up the planner. After delaying
building the RelOptInfo and RangeTblEntry, with my 10k partition
setup, the planner SELECT became 1600 times faster. UPDATE did not
finish in the unpatched version, so gains there are harder to measure.
There's still much work to do on these patches, and there's still more
performance to squeeze out too. Hopefully, I'll be discussing this on
another thread soon.)

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