Re: alternative compression algorithms?

On 04/30/15 02:42, Robert Haas wrote:
> On Wed, Apr 29, 2015 at 6:55 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> I'm not convinced not compressing the data is a good idea - it suspect it
>> would only move the time to TOAST, increase memory pressure (in general and
>> in shared buffers). But I think that using a more efficient compression
>> algorithm would help a lot.
>>
>> For example, when profiling the multivariate stats patch (with multiple
>> quite large histograms), the pglz_decompress is #1 in the profile, occupying
>> more than 30% of the time. After replacing it with the lz4, the data are bit
>> larger, but it drops to ~0.25% in the profile and planning the drops
>> proportionally.
>
> That seems to imply a >100x improvement in decompression speed. Really???

Sorry, that was a bit misleading over-statement. The profiles (same
dataset, same workload) look like this:

pglz_decompress
---------------
44.51% postgres [.] pglz_decompress
13.60% postgres [.] update_match_bitmap_histogram
8.40% postgres [.] float8_cmp_internal
7.43% postgres [.] float8lt
6.49% postgres [.] deserialize_mv_histogram
6.23% postgres [.] FunctionCall2Coll
4.06% postgres [.] DatumGetFloat8
3.48% libc-2.18.so [.] __isnan
1.26% postgres [.] clauselist_mv_selectivity
1.09% libc-2.18.so [.] __memcpy_sse2_unaligned

lz4
---
18.05% postgres [.] update_match_bitmap_histogram
11.67% postgres [.] float8_cmp_internal
10.53% postgres [.] float8lt
8.67% postgres [.] FunctionCall2Coll
8.52% postgres [.] deserialize_mv_histogram
5.52% postgres [.] DatumGetFloat8
4.90% libc-2.18.so [.] __isnan
3.92% liblz4.so.1.6.0 [.] 0x0000000000002603
2.08% liblz4.so.1.6.0 [.] 0x0000000000002847
1.81% postgres [.] clauselist_mv_selectivity
1.47% libc-2.18.so [.] __memcpy_sse2_unaligned
1.33% liblz4.so.1.6.0 [.] 0x000000000000260f
1.16% liblz4.so.1.6.0 [.] 0x00000000000025e3
(and then a long tail of other lz4 calls)

The difference used to more significant, but I've done a lot of
improvements in the update_match_bitmap method (so the lz4 methods are
more significant).

The whole script (doing a lot of estimates) takes 1:50 with pglz and
only 1:25 with lz4. That's ~25-30% improvement.

The results are slightly unreliable because collected in a Xen VM, and
the overhead is non-negligible (but the same in both cases). I wouldn't
be surprised if the difference was more significant without the VM.

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services