> Before changing to to INT_MAX: > > thetype | colsize_distribution > ---------+---------------------------- > json | {1741,1767,1854,1904,2292} > jsonb | {3551,5866,5910,5958,6168} > > After: > > thetype | colsize_distribution > ---------+---------------------------- > json | {1741,1767,1854,1904,2292} > jsonb | {3515,3543,3636,3690,4038} > > So that did improve things, just not as much as we'd like.
And with Tom's test patch: 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 ---------------- 541 MB (1 row) thetype | colsize_distribution ---------+---------------------------- json | {1741,1767,1854,1904,2292} jsonb | {2037,2114,2288,2348,2746} Since that improved things a *lot*, just +40% instead of +200%, I thought I'd test some select queries. I decided to test a GIN lookup and value extraction, since indexed lookup is really what I care about. 9.4b2 no patches: 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=20.814..2845.454 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=20.551..20.551 rows=100423 loops=1) Index Cond: (row_to_json @> '{"rpt_per_dt": "2003-06-30"}'::jsonb) Planning time: 0.102 ms Execution time: 2856.179 ms 9.4b2 TL patch: 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=24.071..5201.687 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=23.779..23.779 rows=100423 loops=1) Index Cond: (row_to_json @> '{"rpt_per_dt": "2003-06-30"}'::jsonb) Planning time: 0.098 ms Execution time: 5214.212 ms ... so, an 80% increase in lookup and extraction time for swapping offsets for lengths. That's actually all extraction time; I tried removing the extraction from the query, and without it both queries are close enough to be statstically insignificant. -- 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