Re: INSERT ... ON CONFLICT DO UPDATE

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