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 Heap Scan 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) Planning time: 0.095 ms Execution 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers