Thanks for the info Heikki. However, my concern is the fact that the same insert when ran inside PgAdmin executes at 2000 inserts per second whereas through ODBC it is at 625 inserts per second. It seems that if uuid_generate_v1() was the only bottleneck it would occur inside PgAdmin. Same information, same uuid_generate_v1() default. Any thoughts on why the performance difference between the two?
- Jeremy
> Date: Fri, 28 Feb 2014 19:25:17 +0200 > From: hlinnakangas@vmware.com > To: consolity@outlook.com > CC: pgsql-bugs@postgresql.org; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] UUID, UUID-OSSP extension, and ODBC issue > > On 02/28/2014 06:31 PM, Jeremy Thornton wrote: > > ODBC: pgsql 09.03.0200;OS: Windows 8.1 Pro;Postgres version: 9.3.3; > > In using pgsqlODBC 09.03.0200 > > or any version prior that we have tested we seem to be having a > > performance decrease to only 625 inserts per second when > > using UUID as the primary key and using the UUID-OSSP to generate > > the default value of uuid_generate_v1() whereas if we generate the > > UUID client side for the primary key and send it with the values in > > the insert transaction we can increase it to 1500 inserts per second > > bypassing the UUID-OSSP extension. This is consistent and > > repeatable. The only change being made is Postgres generating the > > UUID using the extension or us passing the value in the insert > > statement. If Postgres generates it internally while inserting using a script through PgAdmin then it can generate at near 2000 inserts per second so > > uuid-ossp shouldn't be the problem since it can keep up inside PgAdmin, but when > > used over default settings in ODBC it is 2/3 slower. The data being > > tested is the same in all cases. I am using a default System DSN on > > Windows 8.1 Pro (no special settings). Is this a bug, expected behavior, or a settings > > adjustment that needs to be made? > > I doubt this is an ODBC issue. I'm guessing that uuid_generate_v1() is > simply quite slow. > > The upcoming PostgreSQL 9.4 will contain a new method for generating > UUIDs: the pgcrypto contrib module will include a gen_random_uuid() > function, which creates v4 random UUIDs. In a quick test, that's a lot > faster than the UUID-OSSP based functions. > > If you need it bad enough and can't wait for 9.4, you could extract that > into a stand-alone module for older version. It's not a lot of code. Or > you could implement such a function in PL/pgSQL using pgcrypto's > get_random_bytes() function. > > - Heikki