To: pgsql-performance@postgresql.org
From: "Sven R. Kunze" <srkunze@mail.de>
Subject: Speeding up JSON + TSQUERY + GIN
Message-ID: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de>
Date: Sun, 26 Feb 2017 14:28:10 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------F0BBF301F859232548E85F09"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------F0BBF301F859232548E85F09
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello everyone,

I am currently evaluating the possibility of using PostgreSQL for 
storing and querying jsonb+tsvector queries. Let's consider this setup:

create table docs (id serial primary key, meta jsonb);
# generate 10M entries, cf. appendix
create index docs_meta_idx ON docs using gin (meta jsonb_path_ops);
create index docs_name_idx ON docs using gin (to_tsvector('english', 
meta->>'name'));
create index docs_address_idx ON docs using gin (to_tsvector('english', 
meta->>'address'));


Testing around with some smaller datasets, functionality-wise it's 
great. However increasing to 10M, things tend to slow down (using 
PostgreSQL 9.5):


explain analyze select id from docs where meta @> '{"age": 20}';
  Planning time: 0.121 ms
  Execution time: 4873.507 ms

explain analyze select id from docs where meta @> '{"age": 20}';
  Planning time: 0.122 ms
  Execution time: 206.289 ms


explain analyze select id from docs where meta @> '{"age": 30}';
  Planning time: 0.109 ms
  Execution time: 7496.886 ms

explain analyze select id from docs where meta @> '{"age": 30}';
  Planning time: 0.114 ms
  Execution time: 1169.649 ms


explain analyze select id from docs where to_tsvector('english', 
meta->>'name') @@ to_tsquery('english', 'john');
  Planning time: 0.179 ms
  Execution time: 10109.375 ms

explain analyze select id from docs where to_tsvector('english', 
meta->>'name') @@ to_tsquery('english', 'john');
Planning time: 0.188 ms
  Execution time: 238.854 ms


Using "select pg_prewarm('docs');" and on any of the indexes doesn't 
help either.
After a "systemctl stop postgresql.service && sync && echo 3 > 
/proc/sys/vm/drop_caches && systemctl start postgresql.service" the 
age=20, 30 or name=john queries are slow again.


Is there a way to speed up or to warm up things permanently?


Regards,
Sven


Appendix I:

example json:

{"age": 20, "name": "Michelle Hernandez", "birth": "1991-08-16", 
"address": "94753 Tina Bridge Suite 318\\nEmilyport, MT 75302"}


Appendix II:


The Python script to generate fake json data. Needs "pip install faker".

 >>> python fake_json.py > test.json  # generates 2M entries; takes some 
time
 >>> cat test.json | psql -c 'copy docs (meta) from stdin'
 >>> cat test.json | psql -c 'copy docs (meta) from stdin'
 >>> cat test.json | psql -c 'copy docs (meta) from stdin'
 >>> cat test.json | psql -c 'copy docs (meta) from stdin'
 >>> cat test.json | psql -c 'copy docs (meta) from stdin'


-- fake_json.py --

import faker, json;
fake = faker.Faker();
for i in range(2*10**6):
     print(json.dumps({"name": fake.name(), "birth": fake.date(), 
"address": fake.address(), "age": 
fake.random_int(0,100)}).replace('\\n', '\\\\n'))


--------------F0BBF301F859232548E85F09
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <tt>Hello everyone,</tt><tt><br>
    </tt><tt><br>
    </tt><tt>I am currently evaluating the </tt><tt>possibility of
      using PostgreSQL for storing and querying jsonb+tsvector queries.
      Let's consider this setup:<br>
      <br>
      create table docs (id serial primary key, meta jsonb);<br>
      # generate 10M entries, cf. appendix<br>
      create index docs_meta_idx ON docs using gin (meta
      jsonb_path_ops);<br>
    </tt><tt>create index docs_name_idx ON docs using gin
      (to_tsvector('english', meta-&gt;&gt;'name'));<br>
      create index docs_address_idx ON docs using gin
      (to_tsvector('english', meta-&gt;&gt;'address'));<br>
      <br>
      <br>
      Testing around with some smaller datasets, functionality-wise it's
      great. However increasing to 10M, things tend to slow down (using
      PostgreSQL 9.5):<br>
      <br>
      <br>
      explain analyze select id from docs where meta @&gt; '{"age":
      20}';<br>
       Planning time: 0.121 ms<br>
       Execution time: 4873.507 ms<br>
      <br>
      explain analyze select id from docs where meta @&gt; '{"age":
      20}';<br>
       Planning time: 0.122 ms<br>
       Execution time: 206.289 ms<br>
      <br>
      <br>
      <br>
      explain analyze select id from docs where meta @&gt; '{"age":
      30}';<br>
       Planning time: 0.109 ms<br>
       Execution time: 7496.886 ms<br>
      <br>
      explain analyze select id from docs where meta @&gt; '{"age":
      30}';<br>
       Planning time: 0.114 ms<br>
       Execution time: 1169.649 ms<br>
      <br>
      <br>
      <br>
      explain analyze select id from docs where to_tsvector('english',
      meta-&gt;&gt;'name') @@ to_tsquery('english', 'john');<br>
       Planning time: 0.179 ms<br>
       Execution time: 10109.375 ms<br>
      <br>
    </tt><tt><tt>explain analyze select id from docs where
        to_tsvector('english', meta-&gt;&gt;'name') @@
        to_tsquery('english', 'john');<br>
         </tt>Planning time: 0.188 ms<br>
       Execution time: 238.854 ms<br>
      <br>
      <br>
      Using "select pg_prewarm('docs');" and on any of the indexes
      doesn't help either.<br>
      After a "systemctl stop postgresql.service &amp;&amp; sync
      &amp;&amp; echo 3 &gt; /proc/sys/vm/drop_caches &amp;&amp;
      systemctl start postgresql.service" the age=20, 30 or name=john
      queries are slow again.<br>
      <br>
      <br>
      Is there a way to speed up or to warm up things permanently?<br>
    </tt><tt><br>
      <br>
    </tt><tt>Regards,</tt><tt><br>
    </tt><tt>Sven</tt><tt><br>
      <br>
      <br>
      Appendix I:<br>
      <br>
      example json:<br>
      <br>
      {"age": 20, "name": "Michelle Hernandez", "birth": "1991-08-16",
      "address": "94753 Tina Bridge Suite 318\\nEmilyport, MT 75302"}<br>
      <br>
      <br>
      <br>
      Appendix II:<br>
      <br>
      <br>
      The Python script to generate fake json data. Needs "pip install
      faker". <br>
      <br>
      &gt;&gt;&gt; python fake_json.py &gt; test.json  # generates 2M
      entries; takes some time<br>
      &gt;&gt;&gt; cat test.json | psql -c 'copy docs (meta) from stdin'<br>
    </tt><tt>&gt;&gt;&gt; cat test.json | psql -c 'copy docs (meta) from
      stdin'</tt><br>
    <tt><tt>&gt;&gt;&gt; cat test.json | psql -c 'copy docs (meta) from
        stdin'<br>
      </tt></tt><tt><tt><tt>&gt;&gt;&gt; cat test.json | psql -c 'copy
          docs (meta) from stdin'<br>
        </tt></tt></tt><tt><tt>&gt;&gt;&gt; cat test.json | psql -c
        'copy docs (meta) from stdin'<br>
      </tt><br>
      <br>
      -- fake_json.py --<br>
      <br>
      import faker, json;<br>
      fake = faker.Faker();<br>
      for i in range(2*10**6):<br>
          print(json.dumps({"name": fake.name(), "birth": fake.date(),
      "address": fake.address(), "age":
      fake.random_int(0,100)}).replace('\\n', '\\\\n'))<br>
      <br>
    </tt>
  </body>
</html>

--------------F0BBF301F859232548E85F09--