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

From: "Andrey V(dot) Lepikhov" <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Юрий Соколов <funny(dot)falcon(at)gmail(dot)com>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Subject: Re: [WIP] [B-Tree] Retail IndexTuple deletion
Date: 2018-07-02 14:29:38
Message-ID: 2ada5790-dffa-a41a-ef84-10f60a525ba6@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 29.06.2018 14:07, Юрий Соколов wrote:
> чт, 28 июн. 2018 г., 8:37 Andrey V. Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru
> <mailto:a(dot)lepikhov(at)postgrespro(dot)ru>>:
>
>
>
> On 28.06.2018 05:00, Peter Geoghegan wrote:
> > On Tue, Jun 26, 2018 at 11:40 PM, Andrey V. Lepikhov
> > <a(dot)lepikhov(at)postgrespro(dot)ru <mailto:a(dot)lepikhov(at)postgrespro(dot)ru>> 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 to be clear on something now: I just don't think that this
> > patch has any chance of getting committed without something like my
> > own patch to go with it. The worst case for your patch without that
> > component is completely terrible. It's not really important for
> you to
> > actually formally make it part of your patch, so I'm not going to
> > insist on that or anything, but the reality is that my patch does not
> > have independent value -- and neither does yours.
> >
> As I wrote before in the last email, I will integrate TID sorting to my
> patches right now. Please, give me access to the last version of your
> code, if it possible.
> You can track the progress at https://github.com/danolivo/postgres git
> repository
>
>
> Peter is absolutely right, imho: tie-breaking by TID within index
>  ordering is inevitable for reliable performance of this patch.
>

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'.

To demonstrate the potential benefits, I did a test:

CREATE TABLE test (id serial primary key, value integer, factor integer);
INSERT INTO test (value, factor) SELECT random()*1e5, random()*1e3 FROM
generate_series(1, 1e7);
CREATE INDEX ON test(value);
VACUUM;
DELETE FROM test WHERE (factor = 1);
VACUUM test;

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.

[1]
https://www.postgresql.org/message-id/CAH2-WzkVb0Kom%3DR%2B88fDFb%3DJSxZMFvbHVC6Mn9LJ2n%3DX%3DkS-Uw%40mail.gmail.com

> With regards,
> Sokolov Yura.

--
Andrey Lepikhov
Postgres Professional:
https://postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
0001-Retail-IndexTuple-Deletion-Access-Method.patch text/x-patch 12.0 KB
0002-Quick-vacuum-strategy.patch text/x-patch 12.3 KB
0003-Ensure-nbtree-leaf-tuple-keys-are-always-unique.patch text/x-patch 96.9 KB
0004-Retail-IndexTuple-Deletion-with-TID-sorting-in-leaf-.patch text/x-patch 3.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2018-07-02 14:30:11 Old small commitfest items
Previous Message Tom Lane 2018-07-02 14:16:22 Re: Should contrib modules install .h files?