Re: jsonb and nested hstore

On 14.3.2014 23:06, Peter Geoghegan wrote:
> For the benefit of anyone that would like to try the patch out, I make
> available a custom format dump of some delicious sample data. I can
> query the sample data as follows on my local installation:
>
> [local]/jsondata=# select count(*) from delicious ;
> count
> ---------
> 1079399
> (1 row)
>
> [local]/jsondata=# \dt+ delicious
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+-----------+-------+-------+---------+-------------
> public | delicious | table | pg | 1174 MB |
> (1 row)
>
> It's available from:
> http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/jsondata.dump

Thanks.

I've been doing some simple queries on this dataset and ISTM there's a
memory leak somewhere in the json code (i.e. something is probably using
a wrong memory context), because this query:

SELECT doc->'title_detail'->'value', COUNT(*)
FROM delicious GROUP BY 1;

results in this:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND

8231 tomas 20 0 5987520 4,645g 6136 R 95,4 60,4 0:37.54
postgres: tomas delicious [local]

I have shared_buffers=1GB and work_mem=64MB, so 4.6GB seems a bit too
much 4.6GB. Actually it grows even further, and then OOM jumps in and
kills the backend like this:

[ 9227.318998] Out of memory: Kill process 8159 (postgres) score 595
or sacrifice child
[ 9227.319000] Killed process 8159 (postgres) total-vm:5920272kB,
anon-rss:4791568kB, file-rss:6192kB

I'm on commit a3115f0d, which is just 2 days old, so I suppose this was
not fixed yet.

regards
Tomas