Horton

Re: [WIP] Effective storage of duplicates in B-tree index.

Thom Brown <thom@linux.com>

View thread View on postgr.es
From: Thom Brown <thom@linux.com>
To: Peter Geoghegan <pg@heroku.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2016-01-28T17:14:10Z
Lists: pgsql-hackers 
On 28 January 2016 at 17:09, Peter Geoghegan <pg@heroku.com> wrote:
> On Thu, Jan 28, 2016 at 9:03 AM, Thom Brown <thom@linux.com> wrote:
>> I'm surprised that efficiencies can't be realised beyond this point.  Your results show a sweet spot at around 1000 / 10000000, with it getting slightly worse beyond that.  I kind of expected a lot of efficiency where all the values are the same, but perhaps that's due to my lack of understanding regarding the way they're being stored.
>
> I think that you'd need an I/O bound workload to see significant
> benefits. That seems unsurprising. I believe that random I/O from
> index writes is a big problem for us.

I was thinking more from the point of view of the index size.  An
index containing 10 million duplicate values is around 40% of the size
of an index with 10 million unique values.

Thom

Commits

  1. Teach pageinspect about nbtree deduplication.

  2. Doc: Fix deduplicate_items index term.

  3. Revise BTP_HAS_GARBAGE nbtree VACUUM comments.

  4. Remove unneeded "pin scan" nbtree VACUUM code.

  5. Cleanup code in reloptions.h regarding reloption handling

  6. Catch invalid typlens in a couple of places

  7. Compute XID horizon for page level index vacuum on primary.

  8. Make heap TID a tiebreaker nbtree index column.

  9. Avoid pin scan for replay of XLOG_BTREE_VACUUM in all cases

  10. Revert buggy optimization of index scans

  11. Restructure index access method API to hide most of it at the C level.

  12. Reduce pinning and buffer content locking for btree scans.

  13. Avoid scanning nulls at the beginning of a btree index scan.