From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | 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-12 19:13:33 |
Message-ID: | 87a8ghom9k.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>>>>> "Jeff" == Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
Jeff> But shouldn't that still leave us with a 75% full index, rather
Jeff> than slightly over 50% full?
Average is usually about 67%-70%. (For capacity estimation I always
assume 66% for a non-sequentially-filled btree.)
Jeff> The leaf pages start at 50%, grow to 100%, then split back to
Jeff> 50%, then grow back to 100%. So the average should be about 75%.
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.
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2016-08-12 19:22:24 | Re: Parallel tuplesort, partitioning, merging, and the future |
Previous Message | Robert Haas | 2016-08-12 19:03:16 | Re: Surprising behaviour of \set AUTOCOMMIT ON |