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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [WIP] [B-Tree] Retail IndexTuple deletion
Date: 2018-09-21 03:52:10
Message-ID: c5a8f52b-6b61-e6f6-aa22-0f4bd0909b63@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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 generation
"CREATE INDEX pgbench_accounts_ext ON public.pgbench_accounts USING
btree (abalance);" # additional index

Comparison with lazy vacuum:

script:
"DELETE FROM pgbench_accounts WHERE (random() < $factor);" # delete a
part of tuples for cleaning strategies comparison
"VACUUM pgbench_accounts;" # check time of vacuum process by bash 'date
+%s%N | cut -b1-13' command

Results:
| $scale=10 | $scale=100 |
$factor| QVAC | LVAC | QVAC | LVAC |
1E-6 | - | - | 284 | 979 |
1E-5 | 78 | 144 | 288 | 1423 |
1E-4 | 72 | 280 | 388 | 3304 |
1E-3 | 189 | 609 | 2294 | 6029 |
1E-2 | 443 | 783 | 54232| 67884|
1E-1 | 1593 | 1237 | 83092| 86104|

where QVAC - forced use of quick vacuum; LVAC - use lazy vacuum for
index cleanup. $factor corresponds a number of vacuumed tuples. For
example, $scale=10, $factor=1E-1 -> 100000 tuples vacuumed. Time
measured in ms.

So, quick strategy can be used in a vacuum process effectively up to
1-2% of DEAD tuples in a relation.

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

Attachment Content-Type Size
v6-0001-Quick-Vacuum-Strategy.patch text/x-patch 11.6 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2018-09-21 04:00:44 Re: generating bootstrap entries for array types
Previous Message Tom Lane 2018-09-21 03:46:54 Re: Strange failure in LWLock on skink in REL9_5_STABLE