Re: 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