From: Sven R. Kunze <srkunze@mail.de>
To: pgsql-performance@postgresql.org
Subject: Speeding up JSON + TSQUERY + GIN
Date: Sun, 26 Feb 2017 14:28:10 +0100
Message-ID: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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'))
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: srkunze@mail.de
Subject: Re: Speeding up JSON + TSQUERY + GIN
In-Reply-To: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox