Geoff Winkless wrote:
> On 20 July 2015 at 14:33, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>
> > If I'm not mistaken, the conclusions from posts in this thread are:
> >
> > 3. there are methods (like cryptographic "random" sequence), which
> > guarantee no conflicts. So one should resort to that.
> >
> >
> Some web research suggests that random sequences are not great for
> indexes because of the resultant "keyspace fragmentation". I'm
> assuming that means a low number of nodes in the btree leafs, so an
> increase in memory usage for the index?
Not sure what type of indexes would be affected by that problem, but I don't think Postgres' btrees would be.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
___
Well, there is a caveat.
If I create table and couple indexes like this:
create table test_index_size(c1 int, c2 int, constraint U2 unique (c2));
create index U1 on test_index_size(c1);
and populate them:
insert into test_index_size(c1, c2)
select round(random()*1000000), a from generate_series(1,1000000) a limit 100000;
and then check the size of the indexes:
for "select pg_relation_size('U1')" I get 2834432
while " select pg_relation_size('U2')" returns 2285568.
So, index based on randomly populated column is bigger than the one based on sequentially populated.
But, on the other hand, after:
reindex table test_index_size;
both indexes are of the same size: 2260992.
Regards,
Igor Neyman