Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-08-30 Thread Peter Geoghegan
On Wed, Aug 29, 2018 at 11:28 PM, Simon Riggs wrote: > If you include heap TID as a column the suffix will be unique and cannot > benefit from suffix truncation. Right. During a page split, we must generate a new high key that's less than or equal to all items on the left side (where the new high

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-08-29 Thread Simon Riggs
On 2 August 2018 at 21:32, Peter Geoghegan wrote: If I want to sort on heap TID as a tie-breaker, I cannot cut any > corners. That is, it has to be just another column, at least as far as > the implementation is concerned (heap TID will have a different > representation in internal pages and leaf

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-08-29 Thread Peter Geoghegan
On Tue, Aug 28, 2018 at 11:32 PM, Peter Eisentraut wrote: > Do you plan to submit this patch to the upcoming commit fest perhaps? I > have done some testing on it and it seems worth pursuing further. I should make sure that this makes it into the September 'fest. Thanks for reminding me. I've be

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-08-28 Thread Peter Eisentraut
Do you plan to submit this patch to the upcoming commit fest perhaps? I have done some testing on it and it seems worth pursuing further. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-08-02 Thread Peter Geoghegan
On Tue, Jul 17, 2018 at 10:42 PM, Simon Riggs wrote: > If we knew that we were never going to do deletes/non-HOT updates from > the table we could continue to use the existing mechanism, otherwise > we will be better off to use sorted index entries. However, it does > appear as if keeping entries

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Simon Riggs
On 17 July 2018 at 23:10, Tom Lane wrote: > Peter Geoghegan writes: >> I've done plenty of research into the history of this hack. It was >> your work, but it does actually make sense in the context of today's >> nbtree code. It is essential with scankey-wise duplicates, since >> groveling throug

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Peter Geoghegan
On Tue, Jul 17, 2018 at 5:45 PM, Robert Haas wrote: > Yes, that's a good point. Also, and I think pretty importantly, this > seems essential if we want to allow retail index tuple deletion, which > has its own set of advantages. Retail index tuple deletion is clearly something we should have in

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Robert Haas
On Tue, Jul 17, 2018 at 5:16 PM, Peter Geoghegan wrote: > There is actually a flipside to that downside, though (i.e. the > downside is also an upside): While not filling up leaf pages that have > free space on them is bad, it's only bad when it doesn't leave the > pages completely empty. Leaving

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Peter Geoghegan
On Tue, Jul 17, 2018 at 3:10 PM, Tom Lane wrote: > Well, the actual problem was O(N^2) behavior: > > https://www.postgresql.org/message-id/2378.967216388%40sss.pgh.pa.us > > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=40549e9cb5abd2986603883e4ab567dab34723c6 Oh, yeah. We st

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Tom Lane
Peter Geoghegan writes: > I've done plenty of research into the history of this hack. It was > your work, but it does actually make sense in the context of today's > nbtree code. It is essential with scankey-wise duplicates, since > groveling through hundreds or even thousands of pages full of > d

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Peter Geoghegan
On Tue, Jul 17, 2018 at 1:29 PM, Tom Lane wrote: > Yeah ... if memory serves, there were specific usage patterns where > that hack made things way better than they'd been before. (I do not > recall if the hack itself was mine, but I think I can be blamed for > the "getting tired" comment ...) I'

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Peter Geoghegan
On Tue, Jul 17, 2018 at 1:12 PM, Robert Haas wrote: > This seems like really interesting and important work. I wouldn't > have foreseen that the "getting tired" code would have led to this > kind of bloat (even if I had known about it at all). Thanks! I'm glad that I can come up with concrete,

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Tom Lane
Robert Haas writes: > On Sun, Jul 8, 2018 at 7:59 PM, Peter Geoghegan wrote: >> The whole "getting tired" thing is the root of the problem here, which >> is why the pending v3 of my patch will remove that code completely >> (_bt_findinsertloc() is streamlined). > This seems like really interesti

Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-17 Thread Robert Haas
On Sun, Jul 8, 2018 at 7:59 PM, Peter Geoghegan wrote: > The whole "getting tired" thing is the root of the problem here, which > is why the pending v3 of my patch will remove that code completely > (_bt_findinsertloc() is streamlined). Peter, This seems like really interesting and important wor

"Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

2018-07-08 Thread Peter Geoghegan
On Wed, Jul 4, 2018 at 9:43 AM, Peter Geoghegan wrote: > I'm starting this new thread to discuss the benefits of B-Tree suffix > truncation, and to demonstrate how effective it can be at increasing > fan-out and space utilization with a real-world example. I haven't > explained why suffix truncati

Re: Why B-Tree suffix truncation matters

2018-07-05 Thread Peter Geoghegan
On Thu, Jul 5, 2018 at 7:17 AM, Andrey Borodin wrote: > Some years ago I've observed viable performance improvement (around 8% in > inserts and 5% in selects) of covering indexes in a series of experiments [0]. Covering indexes are definitely a closely related idea. It kind of makes sense that w

Re: Why B-Tree suffix truncation matters

2018-07-05 Thread Andrey Borodin
Hi, Peter! > 4 июля 2018 г., в 20:43, Peter Geoghegan написал(а): > > I've been working on B-Tree suffix truncation Thank you for this detailed explanation. I must admit I've been seriously confusing prefix truncation and suffix truncation before this post. Some years ago I've observed viable

Why B-Tree suffix truncation matters

2018-07-04 Thread Peter Geoghegan
I've been working on B-Tree suffix truncation, as part of a larger effort to make all index tuples have unique keys by treating their heap TID as a first class part of the key space, as part of an even larger (and still ill-defined) effort to improve index vacuuming (a.k.a. "retail index tuple dele