Re: UUID v1 optimizations...

Please don't top post -- trim the your response down so that only
still-relevant text remains.

On Tue, Jun 11, 2019 at 1:27 PM Ancoron Luciferis
<ancoron(dot)luciferis(at)googlemail(dot)com> wrote:
> Primary key indexes after an ANALYZE:
> table_name | bloat | index_mb | table_mb
> -------------------+----------------+----------+----------
> uuid_v1 | 767 MiB (49 %) | 1571.039 | 1689.195
> uuid_v1_timestamp | 768 MiB (49 %) | 1571.039 | 1689.195
> uuid_seq | 759 MiB (49 %) | 1562.766 | 1689.195
> uuid_serial | 700 MiB (47 %) | 1504.047 | 1689.195
>
> OK, sadly no reclaim in any of them.

I don't know how you got these figures, but most likely they don't
take into account the fact that the FSM for the index has free blocks
available. You'll only notice that if you have additional page splits
that can recycle that space. Or, you could use pg_freespacemap to get
some idea.

> 5.) REINDEX
> Table: uuid_v1 Time: 21549.860 ms (00:21.550)
> Table: uuid_v1_timestamp Time: 27367.817 ms (00:27.368)
> Table: uuid_seq Time: 19142.711 ms (00:19.143)
> Table: uuid_serial Time: 16889.807 ms (00:16.890)
>
> Even in this case it looks as if my implementation is faster than
> anything else - which I really don't get.

Sorting already-sorted data is faster. CREATE INDEX is mostly a big
sort operation in the case of B-Tree indexes.

> I might implement a different opclass for the standard UUID to enable
> time-wise index sort order. This will naturally be very close to
> physical order but I doubt that this is something I can tell PostgreSQL, or?

PostgreSQL only knows whether or not your page splits occur in the
rightmost page in the index -- it fills the page differently according
to whether or not that is the case.

--
Peter Geoghegan