Re: Speeding up JSON + TSQUERY + GIN

["Sven R. Kunze" <srkunze@xxxxxxx>]

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@xxxxxxx> 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 now before 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))