Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

On 02/17/2015 02:11 AM, Peter Geoghegan wrote:
>
>>> >>Whatever works, really. I can't say that the performance implications
>>> >>of acquiring that hwlock are at the forefront of my mind. I never
>>> >>found that to be a big problem on an 8 core box, relative to vanilla
>>> >>INSERTs, FWIW - lock contention is not normal, and may be where any
>>> >>heavweight lock costs would really be encountered.
>> >
>> >Oh, cool. I guess the fast-path in lmgr.c kicks ass, then :-).
> Seems that way. But even if that wasn't true, it wouldn't matter,
> since I don't see that we have a choice.

I did some quick performance testing on this. For easy testing, I used a 
checkout of git master, and simply added LockAcquire + LockRelease calls 
to ExecInsert, around the heap_insert() call. The test case I used was:

psql -c "create table footest (id serial primary key);"

echo "insert into footest select from generate_series(1, 10000);" > 
inserts.sql

pgbench -n -f inserts.sql postgres -T100 -c4"

With the extra lock calls, I got 56 tps on my laptop. With unpatched git 
master, I got 60 tps. I also looked at the profile with "perf", and 
indeed about 10% of the CPU time was spent in LockAcquire and 
LockRelease together.

So the extra locking incurs about 10% overhead. I think this was pretty 
ḿuch a worst case scenario, but not a hugely unrealistic one - many 
real-world tables have only a few columns, and few indexes. With more 
CPUs you would probably start to see contention, in addition to just the 
extra overhead.

Are we OK with a 10% overhead, caused by the locking? That's probably 
acceptable if that's what it takes to get UPSERT. But it's not OK just 
to solve the deadlock issue with regular insertions into a table with 
exclusion constraints. Can we find a scheme to eliminate that overhead?

- Heikki