From: Sven R. Kunze <srkunze@mail.de>
To: pgsql-performance@postgresql.org
Subject: Re: Speeding up JSON + TSQUERY + GIN
Date: Mon, 27 Feb 2017 15:46:59 +0100
Message-ID: <029cc962-04c8-c6a0-5b3f-8d68ddaff339@mail.de> (raw)
In-Reply-To: <CAF4Au4wVfx1or_DR1P9t4peheOm0ps-1bNhFDXnPdkZGLEpGqQ@mail.gmail.com>
References: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de>
<CAF4Au4wVfx1or_DR1P9t4peheOm0ps-1bNhFDXnPdkZGLEpGqQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@mail.de
> <mailto:srkunze@mail.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))
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: <029cc962-04c8-c6a0-5b3f-8d68ddaff339@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