On 20 July 2015 at 15:07, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
> Not sure what type of indexes would be affected by that problem, but I > don't think Postgres' btrees would be. > I admit it's not really my area. Take it up with Drew Blas, I guess :) https://blog.starkandwayne.com/2015/05/23/uuid-primary-keys-in-postgresql/ (see the addendum near the bottom). My first stab at comprehending what he means: Imagine if you have around a thousand entries in your index. In a sequential set, the btree could be (with each leaf containing 100 values) 499 299 799 100 200 300 400 500 600 700 800 900 In a (16-bit, say) random set, the btree could be 33208 21728 45220 927 15927 21729 29661 33209 34917 40121 45221 49826 Inserting a new value in a sequential key is always going to go into the 900 node. When that fills, you can add a new node and just balance the parent branches (assuming postgres doesn't try to keep a proper balance across the nodes too?). However in the random tree a new value could be inserted into _any_ of the leaf nodes, which means you either have to create more leaf nodes than you require (in order to leave plenty of blank space, and even if you do that you will probably end up with uneven fill, which means some searches will be slower than others) or you end up having to refactor all of your nodes every time you insert a value. Geoff
