Re: jsonb format is pessimal for toast compression

On 08/15/2014 04:19 PM, Tom Lane wrote:
> Personally I'd prefer to go to the all-lengths approach, but a large
> part of that comes from a subjective assessment that the hybrid approach
> is too messy.  Others might well disagree.
> 
> In case anyone else wants to do measurements on some more data sets,
> attached is a copy of Heikki's patch updated to apply against git tip.

Note that this is not 100% comparable because I'm running it against git
clone, and the earlier tests were against beta2.  However, the Heikki
patch looks like a bust on this dataset -- see below.

postgres=# select pg_size_pretty(pg_total_relation_size('jsonic'));pg_size_pretty
----------------394 MB
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('jsonbish'));
pg_size_pretty
----------------542 MB

Extraction Test:

postgres=# explain analyze select row_to_json -> 'kt1_total_sum' from
jsonbish where row_to_json @> '{ "rpt_per_dt" : "2003-06-30" }';
      QUERY
 
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on jsonbish  (cost=29.55..582.92 rows=200 width=18)
 
(actual time=22.742..5281.823 rows=100423 loops=1)  Recheck Cond: (row_to_json @> '{"rpt_per_dt":
"2003-06-30"}'::jsonb) Heap Blocks: exact=1471  ->  Bitmap Index Scan on jsonbish_row_to_json_idx  (cost=0.00..29.50
 
rows=200 width=0) (actual time=22.445..22.445 rows=100423 loops=1)        Index Cond: (row_to_json @> '{"rpt_per_dt":
"2003-06-30"}'::jsonb)Planningtime: 0.095 msExecution time: 5292.047 ms
 
(7 rows)

So, that extraction test is about 1% *slower* than the basic Tom Lane
lengths-only patch, and still 80% slower than original JSONB.  And it's
the same size as the lengths-only version.

Huh?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com