Thread: [PERFORM] Speeding up JSON + TSQUERY + GIN

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'))

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?

On 27.02.2017 19:22, Jeff Janes wrote:

If by 'permanently', you mean even when you intentionally break things, then no.  You will always be able to intentionally break things.  There is on-going discussion of an auto-prewarm feature.  But that doesn't yet exist; and once it does, a super user will always be able to break it.

Presumably you have a use-case in mind other than intentional sabotage of your caches by root.  But, what is it?  If you reboot the server frequently, maybe you can just throw 'select pg_prewarm...' into an init script?

I didn't express myself well enough. pg_prewarm doesn't help to speed up those queries at all.

On 28.02.2017 17:49, Jeff Janes wrote:

Oh.  In my hands, it works very well.  I get 70 seconds to do the {age: 20} query from pure cold caches, versus 1.4 seconds from cold caches which was followed by pg_prewarm('docs','prefetch').

How much RAM do you have?  Maybe you don't have enough to hold the table in RAM.  What kind of IO system?  And what OS?

On my test system:

RAM: 4GB
IO: SSD (random_page_cost = 1.0)
OS: Ubuntu 16.04

On 01.03.2017 18:04, Jeff Janes wrote:

On Wed, Mar 1, 2017 at 6:02 AM, Sven R. Kunze <srkunze@mail.de> wrote:

On 28.02.2017 17:49, Jeff Janes wrote:

Oh.  In my hands, it works very well.  I get 70 seconds to do the {age: 20} query from pure cold caches, versus 1.4 seconds from cold caches which was followed by pg_prewarm('docs','prefetch').

How much RAM do you have?  Maybe you don't have enough to hold the table in RAM.  What kind of IO system?  And what OS?

On my test system:

RAM: 4GB
IO: SSD (random_page_cost = 1.0)
OS: Ubuntu 16.04

4GB is not much RAM to be trying to pre-warm this amount of data into.  Towards the end of the pg_prewarm, it is probably evicting data read in by the earlier part of it.

What is shared_buffers?

942MB.

But I see where you are coming from. How come that these queries need a Recheck Cond? I gather that this would require reading not only the index data but also the table itself which could be huge, right?