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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

After further testing it appears just to be the slow uuid-ossp extensions. Here is some additional results. Because of the difference between the v4/v1 extension times via ODBC I do not think now that ODBC is the problem either. I look forward to when Postgres will have it own native uuid v1 and v4 functions that are faster (more like newid() or newsequentialid() in SQL server). I mean 1,149 and 763 rows per second using the uuid-ossp extension may not be slow by some standards, but we are running a multi-tenant application that requires as much speed as we can pull out of it.
The insert is ran as one transaction.
UUID v4 when generated client side postgres inserts 100,000 rows in 49 seconds (2040 inserts per sec[ips]),UUID v1 when generated client side postgres inserts 100,000 rows in 49 seconds (2040 ips),UUID v4 when generated by uuid_generate_v4() uuid-ossp extension postgres inserts 100,000 rows in 87 seconds (1,149 ips),UUID v1 when generated by uuid_generate_v1() uuid-ossp extension postgres inserts 100,000 rows in 131 seconds (763 ips).
- Jeremy