Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 2018-11-29 14:27:50 +0100, Dmitry Dolgov wrote: > > On Fri, Sep 21, 2018 at 5:52 AM Andrey Lepikhov > > wrote: > > > > The v6 version of quick vacuum, which utilizes the amtargetdelete() > > interface for retail indextuple deletion. > > Now it is more simple and laconic. > > It must be

Re: [WIP] [B-Tree] Retail IndexTuple deletion

> On Fri, Sep 21, 2018 at 5:52 AM Andrey Lepikhov > wrote: > > The v6 version of quick vacuum, which utilizes the amtargetdelete() > interface for retail indextuple deletion. > Now it is more simple and laconic. > It must be applied after Retail-IndexTuple-Deletion-Access-Method.patch. > >

Re: [WIP] [B-Tree] Retail IndexTuple deletion

The v6 version of quick vacuum, which utilizes the amtargetdelete() interface for retail indextuple deletion. Now it is more simple and laconic. It must be applied after Retail-IndexTuple-Deletion-Access-Method.patch. BENCHMARKS: --- Initial script: pgbench -i -s $scale # initial DB

Re: [WIP] [B-Tree] Retail IndexTuple deletion

The next version of amtargetdelete() interface and its implementation for nbtree, devoted to the problem of retail indextuple deletion. Uses 'v5-0001-Make-nbtree-indexes-have-unique-keys-in-tuples' patch [1] to delete all logical duplicates by one tree descent. [1]

Re: [WIP] [B-Tree] Retail IndexTuple deletion

And background worker patch in attachment. 05.09.2018 15:25, Andrey Lepikhov пишет: Hi, I prepared next version of Background worker (cleaner) based on a retail indextuple deletion patch. This version shows stable behavior on regression tests and pgbench workloads. In this version: 1. Only

Re: [WIP] [B-Tree] Retail IndexTuple deletion

Hi, I prepared next version of Background worker (cleaner) based on a retail indextuple deletion patch. This version shows stable behavior on regression tests and pgbench workloads. In this version: 1. Only AccessShareLock are acquired on a cleanup of heap and index relations. 2. Some

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Aug 7, 2018 at 4:19 PM, Andrey Lepikhov wrote: > Hi, > I wrote a background worker (hcleaner) to demonstrate application of Retail > IndexTuple deletion (see patch at attachment). The patch doesn't seem to have the hcleaner code. Could you share it? Regards, -- Masahiko Sawada NIPPON

Re: [WIP] [B-Tree] Retail IndexTuple deletion

09.08.2018 05:19, Peter Geoghegan пишет: On Tue, Aug 7, 2018 at 12:19 AM, Andrey Lepikhov wrote: I wrote a background worker (hcleaner) to demonstrate application of Retail IndexTuple deletion (see patch at attachment). Like Autovacuum it utilizes concept of one launcher and many workers.

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Aug 7, 2018 at 12:19 AM, Andrey Lepikhov wrote: > I wrote a background worker (hcleaner) to demonstrate application of Retail > IndexTuple deletion (see patch at attachment). > Like Autovacuum it utilizes concept of one launcher and many workers. But > one worker correspond to one

Re: [WIP] [B-Tree] Retail IndexTuple deletion

Hi, I wrote a background worker (hcleaner) to demonstrate application of Retail IndexTuple deletion (see patch at attachment). Like Autovacuum it utilizes concept of one launcher and many workers. But one worker correspond to one database. Short description: Backend collects dirty block

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Sat, Jul 21, 2018 at 3:11 AM, Peter Geoghegan wrote: > On Thu, Jul 19, 2018 at 4:29 AM, Masahiko Sawada > wrote: >>> One area that might be worth investigating is retail index tuple >>> deletion performed within the executor in the event of non-HOT >>> updates. Maybe LP_REDIRECT could be

Re: [WIP] [B-Tree] Retail IndexTuple deletion

13.07.2018 00:00, Peter Geoghegan пишет: One area that might be worth investigating is retail index tuple deletion performed within the executor in the event of non-HOT updates. I will try to use this idea. But now I developed a background worker already (experimental state). It clean an

Re: [WIP] [B-Tree] Retail IndexTuple deletion

This v5 version of the patch is intended to use with v3-0001-Make-all-nbtree-index-tuples-have-unique-keys patch [1]. [1] https://www.postgresql.org/message-id/CAH2-WzkmTRXh%3DzyMAUHyG3%3DO-QQip6CJc2VyNijRO-vzgPxmoQ%40mail.gmail.com 13.07.2018 00:00, Peter Geoghegan пишет: On Tue, Jul 3,

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Thu, Jul 19, 2018 at 4:29 AM, Masahiko Sawada wrote: >> One area that might be worth investigating is retail index tuple >> deletion performed within the executor in the event of non-HOT >> updates. Maybe LP_REDIRECT could be repurposed to mean "ghost record", >> at least in unique index

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Jul 3, 2018 at 5:17 AM, Andrey V. Lepikhov wrote: > Done. > Attachment contains an update for use v.2 of the 'Ensure nbtree leaf tuple > keys are always unique' patch. My v3 is still pending, but is now a lot better than v2. There were bugs in v2 that were fixed. One area that might be

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 03.07.2018 00:40, Peter Geoghegan wrote: On Mon, Jul 2, 2018 at 9:28 AM, Peter Geoghegan wrote: Execution time of last "VACUUM test;" command on my notebook was: with bulk deletion: 1.6 s; with Quick Vacuum Strategy: 5.2 s; with Quick Vacuum Strategy & TID sorting: 0.6 s. I'm glad that

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jul 2, 2018 at 9:28 AM, Peter Geoghegan wrote: >> Execution time of last "VACUUM test;" command on my notebook was: >> >> with bulk deletion: 1.6 s; >> with Quick Vacuum Strategy: 5.2 s; >> with Quick Vacuum Strategy & TID sorting: 0.6 s. > > I'm glad that you looked into this. You could

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jul 2, 2018 at 7:29 AM, Andrey V. Lepikhov wrote: > In the new version the patch [1] was used in cooperation with 'retail > indextuple deletion' and 'quick vacuum strategy' patches (see > '0004-Retail-IndexTuple-Deletion-with-TID-sorting-in-leaf-.patch'. Cool. I'm going to post a

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 29.06.2018 14:07, Юрий Соколов wrote: чт, 28 июн. 2018 г., 8:37 Andrey V. Lepikhov >: On 28.06.2018 05:00, Peter Geoghegan wrote: > On Tue, Jun 26, 2018 at 11:40 PM, Andrey V. Lepikhov > mailto:a.lepik...@postgrespro.ru>> wrote: >> I

Re: [WIP] [B-Tree] Retail IndexTuple deletion

чт, 28 июн. 2018 г., 8:37 Andrey V. Lepikhov : > > > On 28.06.2018 05:00, Peter Geoghegan wrote: > > On Tue, Jun 26, 2018 at 11:40 PM, Andrey V. Lepikhov > > wrote: > >> I still believe that the patch for physical TID ordering in btree: > >> 1) has its own value, not only for target deletion, >

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Wed, Jun 27, 2018 at 12:10 PM, Andrey V. Lepikhov wrote: > > > On 23.06.2018 00:43, Peter Geoghegan wrote: >> >> On Fri, Jun 22, 2018 at 4:24 AM, Andrey V. Lepikhov >> wrote: >>> >>> According to your feedback, i develop second version of the patch. >>> In this version: I had quick look at

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Fri, Jun 29, 2018 at 11:04 AM, Andrey V. Lepikhov wrote: > On 29.06.2018 10:00, Kuntal Ghosh wrote: >> >> On Wed, Jun 27, 2018 at 12:10 PM, Andrey V. Lepikhov >> wrote: >>> >>> I prepare third version of the patches. Summary: >>> 1. Mask DEAD tuples at a page during consistency checking (See

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 29.06.2018 10:00, Kuntal Ghosh wrote: On Wed, Jun 27, 2018 at 12:10 PM, Andrey V. Lepikhov wrote: I prepare third version of the patches. Summary: 1. Mask DEAD tuples at a page during consistency checking (See comments for the mask_dead_tuples() function). - ItemIdSetDead(lp); + if

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Wed, Jun 27, 2018 at 12:10 PM, Andrey V. Lepikhov wrote: > I prepare third version of the patches. Summary: > 1. Mask DEAD tuples at a page during consistency checking (See comments for > the mask_dead_tuples() function). - ItemIdSetDead(lp); + if (target_index_deletion_factor > 0) +

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 28.06.2018 05:00, Peter Geoghegan wrote: On Tue, Jun 26, 2018 at 11:40 PM, Andrey V. Lepikhov wrote: I still believe that the patch for physical TID ordering in btree: 1) has its own value, not only for target deletion, 2) will require only a few local changes in my code, and this

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Jun 26, 2018 at 11:40 PM, Andrey V. Lepikhov wrote: > I still believe that the patch for physical TID ordering in btree: > 1) has its own value, not only for target deletion, > 2) will require only a few local changes in my code, > and this patches can be developed independently. I want

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Jun 26, 2018 at 3:31 AM, Masahiko Sawada wrote: > bttargetdelete doesn't delete btree pages even if pages become empty. > I think we should do that. Otherwise empty page never be recycled. But > please note that if we delete btree pages during bttargetdelete, > recyclable pages might not

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 26.06.2018 15:31, Masahiko Sawada wrote: On Fri, Jun 22, 2018 at 8:24 PM, Andrey V. Lepikhov wrote: Hi, According to your feedback, i develop second version of the patch. In this version: 1. High-level functions index_beginscan(), index_rescan() not used. Tree descent made by

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Fri, Jun 22, 2018 at 8:24 PM, Andrey V. Lepikhov wrote: > Hi, > According to your feedback, i develop second version of the patch. > In this version: > 1. High-level functions index_beginscan(), index_rescan() not used. Tree > descent made by _bt_search(). _bt_binsrch() used for positioning on

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 23.06.2018 01:14, Peter Geoghegan wrote: On Fri, Jun 22, 2018 at 12:43 PM, Peter Geoghegan wrote: On Fri, Jun 22, 2018 at 4:24 AM, Andrey V. Lepikhov wrote: According to your feedback, i develop second version of the patch. In this version: 1. High-level functions index_beginscan(),

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Fri, Jun 22, 2018 at 12:43 PM, Peter Geoghegan wrote: > On Fri, Jun 22, 2018 at 4:24 AM, Andrey V. Lepikhov > wrote: >> According to your feedback, i develop second version of the patch. >> In this version: >> 1. High-level functions index_beginscan(), index_rescan() not used. Tree >> descent

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Fri, Jun 22, 2018 at 4:24 AM, Andrey V. Lepikhov wrote: > According to your feedback, i develop second version of the patch. > In this version: > 1. High-level functions index_beginscan(), index_rescan() not used. Tree > descent made by _bt_search(). _bt_binsrch() used for positioning on the >

Re: [WIP] [B-Tree] Retail IndexTuple deletion

Hi, According to your feedback, i develop second version of the patch. In this version: 1. High-level functions index_beginscan(), index_rescan() not used. Tree descent made by _bt_search(). _bt_binsrch() used for positioning on the page. 2. TID list introduced in amtargetdelete() interface.

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On 19.06.2018 04:05, Peter Geoghegan wrote: On Mon, Jun 18, 2018 at 2:54 PM, Peter Geoghegan wrote: On Sun, Jun 17, 2018 at 9:39 PM, Andrey V. Lepikhov wrote: Patch '0001-retail-indextuple-deletion' introduce new function amtargetdelete() in access method interface. Patch

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Jun 19, 2018 at 2:33 AM, Masahiko Sawada wrote: > I think that we do the partial lazy vacuum using visibility map even > now. That does heap pruning, index tuple killing but doesn't advance > relfrozenxid. Right, that's what I was thinking. Opportunistic HOT pruning isn't like vacuuming

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Tue, Jun 19, 2018 at 8:05 AM, Peter Geoghegan wrote: > On Mon, Jun 18, 2018 at 2:54 PM, Peter Geoghegan wrote: >> On Sun, Jun 17, 2018 at 9:39 PM, Andrey V. Lepikhov >> wrote: >>> Patch '0001-retail-indextuple-deletion' introduce new function >>> amtargetdelete() in access method interface.

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 4:05 PM, Peter Geoghegan wrote: > IOW, the approach you've taken in bttargetdelete() isn't quite correct > because you imagine that it's okay to occasionally "lose" the index > tuple that you originally found, and just move on. That needs to be > 100% reliable, or else

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 4:05 PM, Peter Geoghegan wrote: > Finally, doing things this way would let you delete multiple > duplicates in one shot, as I described in an earlier e-mail. Only a > single descent of the tree is needed to delete quite a few index > tuples, provided that they all happen

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 2:54 PM, Peter Geoghegan wrote: > On Sun, Jun 17, 2018 at 9:39 PM, Andrey V. Lepikhov > wrote: >> Patch '0001-retail-indextuple-deletion' introduce new function >> amtargetdelete() in access method interface. Patch >> '0002-quick-vacuum-strategy' implements this function

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 1:42 PM, Claudio Freire wrote: > Actually, once btree tids are sorted, you can continue tree descent > all the way to the exact leaf page that contains the tuple to be > deleted. > > Thus, the single-tuple interface ends up being quite OK. Sure, you can > optimize things a

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 4:59 PM Peter Geoghegan wrote: > > Note, guaranteed allowable time of index scans (used for quick deletion) > > will be achieved by storing equal-key index tuples in physical TID order [2] > > with patch [3]. > > I now have greater motivation to develop that patch into a

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Sun, Jun 17, 2018 at 9:39 PM, Andrey V. Lepikhov wrote: > I have written a code for quick indextuple deletion from an relation by heap > tuple TID. The code relate to "Retail IndexTuple deletion" enhancement of > btree index on postgresql wiki [1]. I knew that somebody would eventually read