Re: Speeding up JSON + TSQUERY + GIN

Thanks Oleg for your reply.

On 26.02.2017 21:13, Oleg Bartunov wrote:
> On Sun, Feb 26, 2017 at 4:28 PM, Sven R. Kunze <srkunze(at)mail(dot)de
> <mailto:srkunze(at)mail(dot)de>>wrote:
>
> 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'));
>
>
> functional index tends to be slow, better use separate column(s) for
> tsvector

Why? Don't we have indexes to make them faster?

The idea is to accelerate all operations as specified (cf. the table
schema below) without adding more and more columns.

> what is full output from explain analyze ?

Okay, let's stick to gin + @> operator for nowbefore we tackle the
functional index issue.
Maybe, I did something wrong while defining the gin indexes:

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on docs (cost=86.50..9982.50 rows=10000 width=4)
(actual time=97.443..8073.983 rows=98385 loops=1)
Recheck Cond: (meta @> '{"age": 40}'::jsonb)
Heap Blocks: exact=79106
-> Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=10000
width=0) (actual time=66.878..66.878 rows=98385 loops=1)
Index Cond: (meta @> '{"age": 40}'::jsonb)
Planning time: 0.118 ms
Execution time: 8093.533 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on docs (cost=86.50..9982.50 rows=10000 width=4)
(actual time=99.527..3349.001 rows=98385 loops=1)
Recheck Cond: (meta @> '{"age": 40}'::jsonb)
Heap Blocks: exact=79106
-> Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=10000
width=0) (actual time=68.503..68.503 rows=98385 loops=1)
Index Cond: (meta @> '{"age": 40}'::jsonb)
Planning time: 0.113 ms
Execution time: 3360.773 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on docs (cost=86.50..9982.50 rows=10000 width=4)
(actual time=64.928..168.311 rows=98385 loops=1)
Recheck Cond: (meta @> '{"age": 40}'::jsonb)
Heap Blocks: exact=79106
-> Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=10000
width=0) (actual time=45.340..45.340 rows=98385 loops=1)
Index Cond: (meta @> '{"age": 40}'::jsonb)
Planning time: 0.121 ms
Execution time: 171.098 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on docs (cost=86.50..9982.50 rows=10000 width=4)
(actual time=86.118..215.755 rows=98385 loops=1)
Recheck Cond: (meta @> '{"age": 40}'::jsonb)
Heap Blocks: exact=79106
-> Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=10000
width=0) (actual time=54.535..54.535 rows=98385 loops=1)
Index Cond: (meta @> '{"age": 40}'::jsonb)
Planning time: 0.127 ms
Execution time: 219.746 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on docs (cost=86.50..9982.50 rows=10000 width=4)
(actual time=83.197..211.840 rows=98385 loops=1)
Recheck Cond: (meta @> '{"age": 40}'::jsonb)
Heap Blocks: exact=79106
-> Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=10000
width=0) (actual time=53.036..53.036 rows=98385 loops=1)
Index Cond: (meta @> '{"age": 40}'::jsonb)
Planning time: 0.127 ms
Execution time: 215.753 ms
(7 rows)

Regards,
Sven

Table Schema:

Table "public.docs"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('docs_id_seq'::regclass)
meta | jsonb |
Indexes:
"docs_pkey" PRIMARY KEY, btree (id)
"docs_address_idx" gin (to_tsvector('english'::regconfig, meta ->>
'address'::text))
"docs_address_trgm_idx" gin ((meta ->> 'address'::text) gin_trgm_ops)
"docs_birth_idx" btree ((meta ->> 'birth'::text))
"docs_meta_idx" gin (meta jsonb_path_ops)
"docs_name_idx" gin (to_tsvector('english'::regconfig, meta ->>
'name'::text))