Content-Type: multipart/mixed; boundary="------------1dANcLe0OxocWAmiGAnxoFxf"
Message-ID: <84aa2da0-004d-c8f8-48c8-aa2861d1ff73@enterprisedb.com>
Date: Thu, 4 Nov 2021 21:47:16 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.1.0
Subject: Re: pglz compression performance, take two
Content-Language: en-US
To: Mark Dilger <mark.dilger@enterprisedb.com>,
 Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Justin Pryzby <pryzby@telsasoft.com>, pgsql-hackers@postgresql.org
References: <FEF3DC5E-4BC4-44E1-8DEB-DADC67046FE3@yandex-team.ru>
 <D75ABE30-2AAA-47DB-A89F-665E03BAAD2A@yandex-team.ru>
 <25991595-1848-4178-AA57-872B10309DA2@yandex-team.ru>
 <20201230043930.GG25152@telsasoft.com>
 <BF0A7E6D-AB21-47F8-A5FA-328CAA32DB12@yandex-team.ru>
 <20210122024811.GE27167@telsasoft.com>
 <EB24F56E-901B-46D6-BFFC-71D51603ABDE@enterprisedb.com>
 <88C049C6-1B86-4EF7-940D-585B07FEB81B@yandex-team.ru>
 <90649B7F-EF71-40F7-93D0-1029AD0D90F3@enterprisedb.com>
 <B1D3105C-D68B-445F-B3DF-E3F41FE79C46@enterprisedb.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <B1D3105C-D68B-445F-B3DF-E3F41FE79C46@enterprisedb.com>
Archived-At: 
 <https://www.postgresql.org/message-id/84aa2da0-004d-c8f8-48c8-aa2861d1ff73%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------1dANcLe0OxocWAmiGAnxoFxf
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I've looked at this patch again and did some testing. I don't have any 
comments to the code (I see there are two comments from Mark after the 
last version, though).

For the testing, I did a fairly simple benchmark loading either random 
or compressible data into a bytea column. The tables are defined as 
unlogged, the values are 1kB, 4kB and 1MB, and the total amount of data 
is always 1GB. The timings are

     test           master     patched    delta
     ------------------------------------------
     random_1k      12295        12312     100%
     random_1m      12999        12984     100%
     random_4k      16881        15959      95%
     redundant_1k   12308        12348     100%
     redundant_1m   16632        14072      85%
     redundant_4k   16798        13828      82%

I ran the test on multiple x86_64 machines, but the behavior is almost 
exactly the same.

This shows there's no difference for 1kB (expected, because this does 
not exceed the ~2kB TOAST threshold). For random data in general the 
difference is pretty negligible, although it's a bit strange it takes 
longer for 4kB than 1MB ones.

For redundant (highly compressible) values, there's quite significant 
speedup between 15-18%. Real-world data are likely somewhere between, 
but the speedup is still pretty nice.

Andrey, can you update the patch per Mark's review? I'll do my best to 
get it committed sometime in this CF.

Attached are the two scripts used for generating / testing (you'll have 
to fix some hardcoded paths, but simple otherwise).

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------1dANcLe0OxocWAmiGAnxoFxf
Content-Type: application/sql; name="generator.sql"
Content-Disposition: attachment; filename="generator.sql"
Content-Transfer-Encoding: base64

Y3JlYXRlIG9yIHJlcGxhY2UgZnVuY3Rpb24gcmFuZG9tX2J5dGVhKHBfbGVuIGludCkgcmV0
dXJucyBieXRlYSBhcyAkJApkZWNsYXJlCiAgciBieXRlYTsKYmVnaW4KCiAgc2VsZWN0ICgn
XHgnIHx8IHN0cmluZ19hZ2cobWQ1KHJhbmRvbSgpOjp0ZXh0KSwnJykpOjpieXRlYSBpbnRv
IHIKICAgIGZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsIDIgKiBwX2xlbiAvIDMyKSBzKGkpOwoK
ICByZXR1cm4gcjsKCmVuZDsKJCQgbGFuZ3VhZ2UgcGxwZ3NxbDsKCmRyb3AgdGFibGUgdGVz
dF9yYW5kb21fYnl0ZWFfMWs7CmRyb3AgdGFibGUgdGVzdF9yYW5kb21fYnl0ZWFfNGs7CmRy
b3AgdGFibGUgdGVzdF9yYW5kb21fYnl0ZWFfMW07CgpjcmVhdGUgdW5sb2dnZWQgdGFibGUg
dGVzdF9yYW5kb21fYnl0ZWFfMWsgKHYgYnl0ZWEpOwpjcmVhdGUgdW5sb2dnZWQgdGFibGUg
dGVzdF9yYW5kb21fYnl0ZWFfNGsgKHYgYnl0ZWEpOwpjcmVhdGUgdW5sb2dnZWQgdGFibGUg
dGVzdF9yYW5kb21fYnl0ZWFfMW0gKHYgYnl0ZWEpOwoKaW5zZXJ0IGludG8gdGVzdF9yYW5k
b21fYnl0ZWFfMWsgc2VsZWN0IHJhbmRvbV9ieXRlYSgxMDI0KSBmcm9tIGdlbmVyYXRlX3Nl
cmllcygxLDEwMjQqMTAyNCkgcyhpKTsKaW5zZXJ0IGludG8gdGVzdF9yYW5kb21fYnl0ZWFf
NGsgc2VsZWN0IHJhbmRvbV9ieXRlYSg0MDk2KSBmcm9tIGdlbmVyYXRlX3NlcmllcygxLDEw
MjQqMTAyNC80KSBzKGkpOwppbnNlcnQgaW50byB0ZXN0X3JhbmRvbV9ieXRlYV8xbSBzZWxl
Y3QgcmFuZG9tX2J5dGVhKDEwMjQqMTAyNCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDI0
KSBzKGkpOwoKY29weSB0ZXN0X3JhbmRvbV9ieXRlYV8xayB0byAnL21udC9yYWlkL2NvbXBy
ZXNzL3Rlc3RfcmFuZG9tX2J5dGVhXzFrLmRhdGEnOwpjb3B5IHRlc3RfcmFuZG9tX2J5dGVh
XzRrIHRvICcvbW50L3JhaWQvY29tcHJlc3MvdGVzdF9yYW5kb21fYnl0ZWFfNGsuZGF0YSc7
CmNvcHkgdGVzdF9yYW5kb21fYnl0ZWFfMW0gdG8gJy9tbnQvcmFpZC9jb21wcmVzcy90ZXN0
X3JhbmRvbV9ieXRlYV8xbS5kYXRhJzsKCmNyZWF0ZSBvciByZXBsYWNlIGZ1bmN0aW9uIHJl
ZHVuZGFudF9ieXRlYShwX2xlbiBpbnQpIHJldHVybnMgYnl0ZWEgYXMgJCQKZGVjbGFyZQog
IHIgYnl0ZWE7CmJlZ2luCgogIHNlbGVjdCAoJ1x4JyB8fCByZXBlYXQobWQ1KHJhbmRvbSgp
Ojp0ZXh0KSwgMiAqIHBfbGVuIC8gMzIpKTo6Ynl0ZWEgaW50byByOwoKICByZXR1cm4gcjsK
CmVuZDsKJCQgbGFuZ3VhZ2UgcGxwZ3NxbDsKCmRyb3AgdGFibGUgdGVzdF9yZWR1bmRhbnRf
Ynl0ZWFfMWs7CmRyb3AgdGFibGUgdGVzdF9yZWR1bmRhbnRfYnl0ZWFfNGs7CmRyb3AgdGFi
bGUgdGVzdF9yZWR1bmRhbnRfYnl0ZWFfMW07CgpjcmVhdGUgdW5sb2dnZWQgdGFibGUgdGVz
dF9yZWR1bmRhbnRfYnl0ZWFfMWsgKHYgYnl0ZWEpOwpjcmVhdGUgdW5sb2dnZWQgdGFibGUg
dGVzdF9yZWR1bmRhbnRfYnl0ZWFfNGsgKHYgYnl0ZWEpOwpjcmVhdGUgdW5sb2dnZWQgdGFi
bGUgdGVzdF9yZWR1bmRhbnRfYnl0ZWFfMW0gKHYgYnl0ZWEpOwoKaW5zZXJ0IGludG8gdGVz
dF9yZWR1bmRhbnRfYnl0ZWFfMWsgc2VsZWN0IHJlZHVuZGFudF9ieXRlYSgxMDI0KSBmcm9t
IGdlbmVyYXRlX3NlcmllcygxLDEwMjQqMTAyNCkgcyhpKTsKaW5zZXJ0IGludG8gdGVzdF9y
ZWR1bmRhbnRfYnl0ZWFfNGsgc2VsZWN0IHJlZHVuZGFudF9ieXRlYSg0MDk2KSBmcm9tIGdl
bmVyYXRlX3NlcmllcygxLDEwMjQqMTAyNC80KSBzKGkpOwppbnNlcnQgaW50byB0ZXN0X3Jl
ZHVuZGFudF9ieXRlYV8xbSBzZWxlY3QgcmVkdW5kYW50X2J5dGVhKDEwMjQqMTAyNCkgZnJv
bSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDI0KSBzKGkpOwoKY29weSB0ZXN0X3JlZHVuZGFudF9i
eXRlYV8xayB0byAnL21udC9yYWlkL2NvbXByZXNzL3Rlc3RfcmVkdW5kYW50X2J5dGVhXzFr
LmRhdGEnOwpjb3B5IHRlc3RfcmVkdW5kYW50X2J5dGVhXzRrIHRvICcvbW50L3JhaWQvY29t
cHJlc3MvdGVzdF9yZWR1bmRhbnRfYnl0ZWFfNGsuZGF0YSc7CmNvcHkgdGVzdF9yZWR1bmRh
bnRfYnl0ZWFfMW0gdG8gJy9tbnQvcmFpZC9jb21wcmVzcy90ZXN0X3JlZHVuZGFudF9ieXRl
YV8xbS5kYXRhJzsK
--------------1dANcLe0OxocWAmiGAnxoFxf
Content-Type: application/x-shellscript; name="test.sh"
Content-Disposition: attachment; filename="test.sh"
Content-Transfer-Encoding: base64

IyEvYmluL2Jhc2gKCmZvciB0IGluIHRlc3RfcmFuZG9tX2J5dGVhXzFrIHRlc3RfcmFuZG9t
X2J5dGVhXzRrIHRlc3RfcmFuZG9tX2J5dGVhXzFtIHRlc3RfcmVkdW5kYW50X2J5dGVhXzFr
IHRlc3RfcmVkdW5kYW50X2J5dGVhXzRrIHRlc3RfcmVkdW5kYW50X2J5dGVhXzFtOyBkbwoK
CWZvciByIGluIGBzZXEgMSAxMGA7IGRvCgoJCXBzcWwgdGVzdCAtYyAiZHJvcCB0YWJsZSAk
dCIgPiAvZGV2L251bGwgMj4mMTsKCQlwc3FsIHRlc3QgLWMgImNyZWF0ZSB1bmxvZ2dlZCB0
YWJsZSAkdCAodiBieXRlYSkiID4gL2Rldi9udWxsIDI+JjEKCgkJcHNxbCB0ZXN0ID4gdGlt
aW5nLmxvZyA8PEVPRgpcdGltaW5nCmNvcHkgJHQgZnJvbSAnL21udC9yYWlkL2NvbXByZXNz
LyR0LmRhdGEnOwpFT0YKCgkJeD1gZ3JlcCAnVGltZScgdGltaW5nLmxvZyB8IGF3ayAne3By
aW50ICQyfSdgCgoJCXBzcWwgdGVzdCAtYyAiYW5hbHl6ZSAkdCIgPiAvZGV2L251bGwgMj4m
MQoKCQl6PWBwc3FsIC10IC1BIHRlc3QgLWMgInNlbGVjdCBwZ190b3RhbF9yZWxhdGlvbl9z
aXplKCckdCcpImAKCgkJZWNobyAkdCAkciAkeCAkegoKCWRvbmUKCmRvbmUK
--------------1dANcLe0OxocWAmiGAnxoFxf--