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
generateat 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