BUG #13749: PostgreSQL cann't compress random data?

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13749: PostgreSQL cann't compress random data?
Date: 2015-10-30 01:56:46
Message-ID: 20151030015646.3021.82858@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13749
Logged by: digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.4.5
Operating system: CentOS 6.x x64
Description:

postgres=# create table test_plain(c text);
CREATE TABLE
postgres=# alter table test_plain alter column c set storage external;
ALTER TABLE
postgres=# create table test_compress(c text);
CREATE TABLE
postgres=# alter table test_compress alter column c set storage extended;
ALTER TABLE
postgres=# insert into test_plain select repeat(md5(random()::text),10000)
from generate_series(1,1000);
INSERT 0 1000
postgres=# insert into test_compress select
repeat(md5(random()::text),10000) from generate_series(1,1000);
INSERT 0 1000
postgres=# select pg_size_pretty(pg_total_relation_size('test_compress'));
pg_size_pretty
----------------
4168 kB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('test_plain'));
pg_size_pretty
----------------
317 MB
(1 row)

the above case use repeat generate a big data.
but when i use the other case, pgsql cann't compress the data, is the
compress algorithm bug?
postgres=# truncate test_compress ;
TRUNCATE TABLE
postgres=# truncate test_plain ;
TRUNCATE TABLE
postgres=# do language plpgsql $$
declare
begin
for i in 1..100 loop
insert into test_compress select string_agg(md5(random()::text),' ') v
from generate_series(1,10000);
end loop;
end;
$$;
DO
postgres=# do language plpgsql $$ declare begin for i in 1..100 loop insert
into test_plain select string_agg(md5(random()::text),' ') v from
generate_series(1,10000); end loop; end; $$;
DO

postgres=# select pg_size_pretty(pg_total_relation_size('test_compress'));
pg_size_pretty
----------------
33 MB
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('test_plain'));
pg_size_pretty
----------------
33 MB
(1 row)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message daveg 2015-10-30 13:32:52 BUG #13750: Autovacuum slows down with large numbers of tables. More workers makes it slower.
Previous Message Tom Lane 2015-10-30 01:22:51 Re: BUG #13748: Syntax error not emitted