Re: Changes improve the performance of INSERT and UPDATE

Hiroki Kataoka <kataoka(at)interwiz(dot)jp> writes:
>>> This small patch improves the performance of INSERT and UPDATE. By my
>>> machine, these changes raised the performance about 5%~10% in pgbench.
>>
>> BTW, in profiling the backend I've never seen PageAddItem take more than
>> about 1% of the runtime, and in pgbench in particular it seems to be
>> down around 0.1% ... so the above seems a bit optimistic ...

> I have the nearly same result, but pgbench says different. I don't know
> why my test generates 5~10% performance improvement. Therefore, I want
> to take a benchmark in a reliable environment.

I've been testing this patch a bit, and I'm unable to measure any
consistent improvement in pgbench times (sometimes it seems to win,
and some other times it doesn't). And gprof still swears up and down
that PageAddItem is only about 0.1% of the runtime, which would make
it impossible to obtain more than an 0.1% speedup. I'm inclined to
write off your result as measurement error --- it's notoriously hard
to get reproducible results out of pgbench.

> By reference, PageAddItem takes 4%~5% of the runtime in the heavy
> writing operation likes CREATE TABLE AS SELECT.

I tried making a million-row table with just two int4 columns and then
duplicating it with CREATE TABLE AS SELECT. In this context gprof
shows PageAddItem as taking 7% of the runtime, which your patch knocks
down to 1.5%. This seems to be about the best possible real-world case,
though (the wider the rows, the fewer times PageAddItem can loop), and
so I'm still unconvinced that there's a generic gain here. Adding an
additional word to page headers has a very definite cost --- we can
assume about a .05% increase in net I/O demands across *every*
application, whether they do a lot of inserts or not --- and so a
patch that provides a noticeable improvement in only a very small set
of circumstances is going to have to be rejected.

Has anyone else experimented with this patch? Have you gotten any
better impression of the cost/benefit ratio than I'm getting?

regards, tom lane

PS: If we were going to apply the patch, I'd be inclined to compensate
for the space usage by removing the pd_tli field, which isn't actually
ever used anywhere in the current code. Then the argument would become
one about opportunity costs --- will we ever need pd_tli in the future?
I don't think we yet have enough experience with the "timeline" feature
to be sure either way.