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