Обсуждение: 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

On 02/28/2014 07:49 PM, Jeremy Thornton wrote:
> 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?

Oh, I misunderstood the issue then. How exactly are you executing the
inserts? Do you run all the inserts as a single transaction (that's
faster than separate transactions)? If you can create a self-contained
ODBC test program and post it to the list, I can take a closer look...

- Heikki