Hi,
I have finally found some time to implement a custom data type optimized
for version 1 UUID's (timestamp, clock sequence, node):
https://github.com/ancoron/pg-uuid-v1
Some tests (using a few millions of rows) have shown the following
results (when used as a primary key):
COPY ... FROM: ~7.8x faster (from file - SSD)
COPY ... TO : ~1.5x faster (no where clause, sequential output)
The best thing is that for INSERT's there is a very high chance of
hitting the B-Tree "fastpath" because of the timestamp being the most
significant part of the data type, which tends to be increasing.
This also results in much lower "bloat", where the standard "uuid" type
easily goes beyond 30%, the "uuid_v1" should be between 10 and 20%.
Additionally, it also reveals the massive performance degrade I saw in
my tests for standard UUID's:
Initial 200 million rows: ~ 80k rows / second
Additional 17 million rows: ~26k rows / second
...and the new data type:
Initial 200 million rows: ~ 623k rows / second
Additional 17 million rows: ~618k rows / second
The data type also has functions to extract the three parts and has an
additional set of operators to compare it to timestamps for time-series
queries.
Other test results which are of interest:
ANALYZE: essentially equal with uuid_v1 to be just slightly faster
VACUUM: ~4-5x faster
REINDEX: only slightly faster (~10-20%)
I think there's also something from it for a faster standard UUID
implementation as a char array is not very compute friendly and
conversion from or to strings (in/out) can be optimized.
Cheers,
Ancoron
Ref:
Latest test results:
https://gist.github.com/ancoron/d5114b0907e8974b6808077e02f8d109