Re: jsonb format is pessimal for toast compression

On Thu, Aug 21, 2014 at 6:20 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> On 08/20/2014 03:42 PM, Arthur Silva wrote:
> > What data are you using right now Josh?
>
> The same data as upthread.
>
> Can you test the three patches (9.4 head, 9.4 with Tom's cleanup of
> Heikki's patch, and 9.4 with Tom's latest lengths-only) on your workload?
>
> I'm concerned that my workload is unusual and don't want us to make this
> decision based entirely on it.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

Here's my test results so far with the github archive data.

It's important to keep in mind that the PushEvent event objects that I use
in the queries only contains a small number of keys (8 to be precise), so
these tests don't really stress the changed code.

Anyway, in this dataset (with the small objects) using the all-lengths
patch provide small compression savings but the overhead is minimal.

----------------

Test data: 610MB of Json -- 341969 items

Index size (jsonb_ops): 331MB

Test query 1: SELECT data->'url', data->'actor' FROM t_json WHERE data @>
'{"type": "PushEvent"}'
Test query 1 items: 169732

Test query 2: SELECT data FROM t_json WHERE data @> '{"type": "PushEvent"}'
Test query 2 items:

----------------
HEAD (aka, all offsets) EXTENDED
Size: 374MB
Toast Size: 145MB

Test query 1 runtime: 680ms
Test query 2 runtime: 405ms
----------------
HEAD (aka, all offsets) EXTERNAL
Size: 366MB
Toast Size: 333MB

Test query 1 runtime: 505ms
Test query 2 runtime: 350ms
----------------
All Lengths (Tom Lane patch) EXTENDED
Size: 379MB
Toast Size: 108MB

Test query 1 runtime: 720ms
Test query 2 runtime: 420ms
----------------
All Lengths (Tom Lane patch) EXTERNAL
Size: 366MB
Toast Size: 333MB

Test query 1 runtime: 525ms
Test query 2 runtime: 355ms

--
Arthur Silva