From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Greg Stark <stark(at)mit(dot)edu> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>, kskim(at)bitnine(dot)net, Lukas Fittl <lukas(at)fittl(dot)com>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2) |
Date: | 2016-08-13 00:18:34 |
Message-ID: | 87oa4xmss7.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>>>>> "Greg" == Greg Stark <stark(at)mit(dot)edu> writes:
>> No, because as the pages split, they fill more slowly (because there
>> are now more pages). So on average in a large randomly filled index,
>> pages spend more time nearer 50% full than 100% full. This is easy
>> to demonstrate by creating a table with an indexed float8 column and
>> adding batches of random() values to it, checking with pgstatindex
>> at intervals - the average leaf density will rarely exceed 70%.
>>
>> However, worst case conditions can give lower leaf densities;
>> obviously the worst case is if the data is loaded in an order and
>> quantity that just happens to leave every leaf page recently split.
Greg> btree pages don't split 50/50 either. They split biased to assume
Greg> the greater side of the split will receive more inserts -- iirc
Greg> 70/30.
Hmm? The code in _bt_findsplitloc and _bt_checksplitloc doesn't seem to
agree with this.
(Inserting on the high leaf page is a special case, which is where the
fillfactor logic kicks in; that's why sequentially filled indexes are
(by default) 90% full rather than 100%. But other pages split into
roughly equal halves.)
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2016-08-13 00:39:30 | Re: Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2) |
Previous Message | Andrew Gierth | 2016-08-13 00:09:50 | Re: No longer possible to query catalogs for index capabilities? |