Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> On Wed, 2007-07-25 at 10:09 -0400, Merlin Moncure wrote:
>>
>> just a small 'me too' here, the RI penalty seems higher than it should
>> be...especially when the foreign key table is very small, and I can
>> see how this would impact benchmarks.
>
> Any measurements to back that up would be appreciated. "Turning it off"
> isn't really a valid comparison because we do want to make the checks
> and expect there to be some cost to that. We just want to quantify the
> cost to allow prioritising our efforts to improve performance on that.

If anyone's interested in this I would be very interested in seeing the
results of your application benchmarks with various parts of the RI checking
code turned off.

Attached is a patch which adds three gucs for profiling purposes which cut off
the RI checks at various stages. To use them you would want to benchmark your
application five times in comparable conditions:

all variables set to 'no'
skip_ri_locks set to 'yes'
skip_ri_queries set to 'yes'
skip_ri_triggers set to 'yes'
no RI constraints at all

The last ought to be nearly identical to the fourth case. Note that it's
really important to repeat your benchmarks several times to ensure that you're
seeing repeatable results. Measuring CPU overhead is pretty tricky since a
single checkpoint or autovacuum run can completely throw off your results.

In my limited testing I found a *huge* effect for batch loads where many
inserts are done in a single transaction. I only see about a 20% hit on
pgbench with RI checks half of which comes from the trigger overhead and about
a quarter of which comes from each of the SPI queries and the locks. I have
some ideas for tackling the SPI queries which would help the batch loading
case but I'm not sure how much resources it makes sense to expend to save 5%
in the OLTP case.