Re: jsonb and nested hstore

On 03/13/2014 08:42 AM, Greg Stark wrote:
> Fwiw the jsonb data doesn't actually seem to be any smaller than text
> json on this data set (this is avg(pg_column_size(col)) and I checked,
> they're both using the same amount of toast space)
>
> jsonb | json
> -------+-------
> 813.5 | 716.3
> (1 row)

That's expected, you save on whitespace, quotes and punctuation and
spend on structural overhead (e.g. string lengths). The actual strings
stored are the virtally the same. Numbers are stored as numerics, which
might or might not be longer. Nulls and booleans are about a wash.

>
> It's still more than 7x faster in cpu costs though:
>
> stark=# select count(attrs->'properties'->>'STREET') from citylots;
> count
> --------
> 196507
> (1 row)
>
> Time: 1026.678 ms
>
> stark=# select count(attrs->'properties'->>'STREET') from citylots_json;
> count
> --------
> 196507
> (1 row)
>
> Time: 7418.010 ms
>

That's also expected, it's one of the major benefits. With jsonb you're
avoiding reparsing the json.

cheers

andrew