Horton

Re: [HACKERS] Custom compression methods

Dilip Kumar <dilipbalaut@gmail.com>

View thread View on postgr.es
From: Dilip Kumar <dilipbalaut@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Alexander Korotkov <a.korotkov@postgrespro.ru>, David Steele <david@pgmasters.net>, Ildus Kurbangaliev <i.kurbangaliev@gmail.com>, Dmitry Dolgov <9erthalion6@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-02-21T12:03:50Z
Lists: pgsql-hackers 
On Sat, Feb 20, 2021 at 11:04 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Sat, Feb 20, 2021 at 2:51 AM Robert Haas <robertmhaas@gmail.com> wrote:
> >
> > On Fri, Feb 19, 2021 at 11:12 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > I think that these performance tests aren't really exercising the
> > expanded-record stuff, just the ExecEvalRow changes. We need to test
> > that test case, and I tend to suspect there's going to be a measurable
> > regression.
>
> I will do testing around this area.

I have done testing for the expanded-record.  Basically I have noticed
there is no performance regression when there are no
compressed/external fields.  But there is a huge regression when there
are compressed data.

Test setup:
----------------
create table foo (a int, b text, c text);
create table bar (f foo);

create or replace function make_foo() returns foo as $$declare x foo;
begin
x.a = 1;
select b,c into x.b, x.c from foo;
return x;
end$$ language plpgsql;

create or replace function test() returns void AS
$$
begin
 for i in 1..100000 loop
 insert into bar select make_foo();
 end loop;
end;
$$ language 'plpgsql';


Testcase: select test();  (every time truncate bar before executing this query)

Case1: No compress/no external
insert into foo select 1, repeat('1234567890', 10), repeat('1234567890', 10);

execution time for "select test()"
Head: 2536.420 ms
patch: 2688.565 ms

Case2: Only compress
insert into foo select 1, repeat('1234567890', 500), repeat('1234567890', 10);

execution time for "select test()"
head: 2545.944 ms
Patch: 9375.524 ms

Case2: compress + external
Alter table foo alter column c set storage external;
insert into foo select 1, repeat('1234567890', 500), repeat('1234567890', 500);

execution time for "select test()"
Head: 10265.052 ms
Patch: 15469.902 ms

Summary:
In this particular path we are only processing the already deformed
tuple that is the reason we are not seeing regression with the
non-compressed data.  But with compressed data we have to give extra
cost for the decompression and that is why there is regression.  But
IMHO with this we are getting one benefit is that now we will not have
individual compressed data inside composite type so next time when we
will have to select from those composite type then we will have to do
less decompression with patch compared to the head so we might gain
there.  I will try to come up with the complete test case where we
will do selection after inserting into the composite type and compare
the performance.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Commits

  1. docs: Update TOAST storage docs for configurable compression.

  2. Further tweaking of pg_dump's handling of default_toast_compression.

  3. Fix interaction of TOAST compression with expression indexes.

  4. Tidy up more loose ends related to configurable TOAST compression.

  5. Short-circuit slice requests that are for more than the object's size.

  6. Mostly-cosmetic adjustments of TOAST-related macros.

  7. Remove useless configure probe for <lz4/lz4.h>.

  8. Error on invalid TOAST compression in CREATE or ALTER TABLE.

  9. docs: Fix omissions related to configurable TOAST compression.

  10. More code cleanup for configurable TOAST compression.

  11. Bring configure support for LZ4 up to snuff.

  12. Make compression.sql regression test independent of default.

  13. Use valid compression method in brin_form_tuple

  14. Fix up pg_dump's handling of per-attribute compression options.

  15. Allow configurable LZ4 TOAST compression.

  16. Fix inconsistencies in the code

  17. Mostly-cosmetic improvements in memory chunk header alignment coding.

  18. Allow numeric to use a more compact, 2-byte header in many cases.