Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ciMbm-0001Is-0q for pgsql-performance@arkaria.postgresql.org; Mon, 27 Feb 2017 14:49:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ciMbl-000380-7N for pgsql-performance@arkaria.postgresql.org; Mon, 27 Feb 2017 14:49:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ciMZx-0008Jd-3W for pgsql-performance@postgresql.org; Mon, 27 Feb 2017 14:47:09 +0000 Received: from shout02.mail.de ([2001:868:100:600::217]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ciMZs-00043D-AV for pgsql-performance@postgresql.org; Mon, 27 Feb 2017 14:47:07 +0000 Received: from postfix01.mail.de (postfix01.bt.mail.de [10.0.121.125]) by shout02.mail.de (Postfix) with ESMTP id 3FC3F698E5 for ; Mon, 27 Feb 2017 15:47:00 +0100 (CET) Received: from smtp01.mail.de (smtp01.bt.mail.de [10.0.121.211]) by postfix01.mail.de (Postfix) with ESMTP id 2943A40110 for ; Mon, 27 Feb 2017 15:47:00 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.de; s=mailde201610; t=1488206820; bh=dGNuOb/50ZEulnJYT/ToQTd9LDPgwpajDO9IDxZdrSc=; h=Subject:To:References:From:Date:In-Reply-To:From; b=THX7qpmqZ9eJWKSjK6/XE/p9hsJww2SDoC78TLyLVSEZVgmE4FQ3j0v4EJX8L4Yze lO3JpfS4h9S4Uk5VELZEoCvTpkGaRBMrFU5qbMFVMjb4KhfrsfdEYSmYmsXyfoVPaD 3KfKpCCzpxt8vYJOL9j74hiQ+nZSI7hH2VWirEDc= Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by smtp01.mail.de (Postfix) with ESMTPSA id 9098C40465 for ; Mon, 27 Feb 2017 15:46:59 +0100 (CET) Subject: Re: Speeding up JSON + TSQUERY + GIN To: pgsql-performance@postgresql.org References: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de> From: "Sven R. Kunze" Message-ID: <029cc962-04c8-c6a0-5b3f-8d68ddaff339@mail.de> Date: Mon, 27 Feb 2017 15:46:59 +0100 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------0A2E586A949CBA4A2828044A" X-purgate: clean X-purgate: This mail is considered clean (visit http://www.eleven.de for further information) X-purgate-type: clean X-purgate-Ad: Categorized by eleven eXpurgate (R) http://www.eleven.de X-purgate: This mail is considered clean (visit http://www.eleven.de for further information) X-purgate: clean X-purgate-size: 15722 X-purgate-ID: 154282::1488206819-0000088C-39D26FC0/0/0 X-Spam-Status: No, score=-1.5 required=10.0 tests=ALL_TRUSTED,BAYES_05, FREEMAIL_FROM,HTML_MESSAGE autolearn=no autolearn_force=no version=3.4.1 X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on spamassassin01.mail.de X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------0A2E586A949CBA4A2828044A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 >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)) --------------0A2E586A949CBA4A2828044A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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> 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))

 --------------0A2E586A949CBA4A2828044A--