Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iidQi-0003Ox-Dk for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 12:00:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iidQg-0007az-Ur for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 12:00:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iiTN5-0003Nx-Nx for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 01:15:55 +0000 Received: from mail-qt1-x841.google.com ([2607:f8b0:4864:20::841]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiTN1-0006Vc-Om for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 01:15:54 +0000 Received: by mail-qt1-x841.google.com with SMTP id n15so9860171qtp.5 for ; Fri, 20 Dec 2019 17:15:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=6o1cJ+GQmbgBwECtFUASPQ61W7zWFv0GZI9xX6SFgv8=; b=VplLCOyCfff6cGrqb5/do1H8K/w8O8RAp+emxWeDJujiZMUT5baWbgftsCKmBZClnr BTClUzSUViAe8NlI+d2V3W+C3Kn99bUoqnffEYW+c34sKq7jqv6HY6qXe1naWPTrncr4 ejUcwVjb4uO0eVJpJEVAEI4VmROqK0YC2Eorm2Ae7fLZK2DbY/nlX48tLUgJ80b1PHPQ Eb3/XbKuY1Cs1kpkGw9aXPre0hmnlr+cRt9Ame9ijReD5W9ySjZs0qqkp7q53/2Wl7t/ De6o4auchwg76rle88Zqyf+ltmLLF7y3Fju6evnILNX3vwsDaN4+aTBYN11PTXmwpeU0 UP1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=6o1cJ+GQmbgBwECtFUASPQ61W7zWFv0GZI9xX6SFgv8=; b=nI74wGot/sw4qFzDMbnRby3eA4vtTXi7i5ka2Nja3kwQpxbiLUgwaWhSFHVW7scsKd C4mKGcecSNoX5tcFyPWL9zBf0WWtKlqTmdiXnCqu9t36YyIfarI6ttGpxfZIYqKwrbTp YgB0zcUQxmKk3zKbW2pqGKzMApPkmTSCKzIOv8usNMTqPzO+ImTXz1WwtKlcHGW2fPJr UXF/aNbn9guJwY+UvYN7CwLtT8IOL+K/PzLYbT27guDqvMz6/OJJoGlt0CaeKxAaauUX ilPcSGWGvh6QsPVDs6a2ZftxnFRxV4mJFPCsS6btZK9+DZsT5/Qc7/8aUlsMZpTKh84m TI4g== X-Gm-Message-State: APjAAAXJNDx1dWWIcRRLDOyRgtO5GmtVQVexeKgyARrakh2rfBCvZW5c XPkb5TKd8cG4bw9g0RuAJMM= X-Google-Smtp-Source: APXvYqy7Cui5gnX+dmy2UHllQORjNMafDqpkYJGSLi2IjYhwNJ8R1kqUNrEVVKaQDCk6T/A8Ixp+DA== X-Received: by 2002:ac8:4708:: with SMTP id f8mr14207667qtp.129.1576890949890; Fri, 20 Dec 2019 17:15:49 -0800 (PST) Received: from [192.168.2.2] (pool-173-79-120-3.washdc.fios.verizon.net. [173.79.120.3]) by smtp.gmail.com with ESMTPSA id u52sm3798950qta.23.2019.12.20.17.15.48 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 20 Dec 2019 17:15:49 -0800 (PST) From: Zhihong Zhang Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_3310C128-C933-4755-8300-770AC5B2946B" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Fri, 20 Dec 2019 20:15:39 -0500 In-Reply-To: <20191220223005.f6w4ff4kdbgtanpz@development> Cc: pgsql-bugs@lists.postgresql.org To: Tomas Vondra References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> X-Mailer: Apple Mail (2.3608.40.2.2.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_3310C128-C933-4755-8300-770AC5B2946B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Run those 2 EXPLAINs with seqscan off and on. See what difference it = makes! SET enable_seqscan =3D off; explain analyze select id, _doc->>'floatValue' from assets where (_doc = #> '{floatValue}'::text[])::double precision < 3.0 limit 100; "Limit (cost=3D0.43..313.25 rows=3D100 width=3D53) (actual = time=3D0.092..0.236 rows=3D7 loops=3D1)" " -> Index Scan using assets_floatvalue_idx on assets = (cost=3D0.43..2714072.57 rows=3D867607 width=3D53) (actual = time=3D0.089..0.230 rows=3D7 loops=3D1)" " Index Cond: (((_doc #> '{floatValue}'::text[]))::double = precision < '3'::double precision)" "Planning Time: 1.617 ms" "Execution Time: 0.276 ms" SET enable_seqscan =3D on; explain analyze select id, _doc->>'floatValue' from assets where (_doc = #> '{floatValue}'::text[])::double precision < 3.0 limit 100; "Limit (cost=3D0.00..107.95 rows=3D100 width=3D53) (actual = time=3D41021.311..313501.746 rows=3D7 loops=3D1)" " -> Seq Scan on assets (cost=3D0.00..936605.40 rows=3D867607 = width=3D53) (actual time=3D41021.309..313501.732 rows=3D7 loops=3D1)" " Filter: (((_doc #> '{floatValue}'::text[]))::double precision < = '3'::double precision)" " Rows Removed by Filter: 2602824" "Planning Time: 0.283 ms" "Execution Time: 313501.777 ms" Zhihong > On Dec 20, 2019, at 5:30 PM, Tomas Vondra = wrote: >=20 > On Fri, Dec 20, 2019 at 04:52:17PM -0500, Zhihong Zhang wrote: >> I have an index on JSONB fields like this, >>=20 >>=20 >>=20 >> CREATE INDEX float_number_index_path2 >>=20 >> ON public.assets USING btree >>=20 >> (((_doc #> '{floatValue}'::text[])::double precision) ASC NULLS = LAST) >>=20 >> TABLESPACE pg_default; >>=20 >>=20 >>=20 >> However query doesn't use it, >>=20 >>=20 >>=20 >> explain select id, _doc->>'floatValue' from assets where (_doc #> >> '{floatValue}'::text[])::double precision < 3.0 limit 3; >>=20 >>=20 >>=20 >> Limit (cost=3D0.00..3.24 rows=3D3 width=3D53) >>=20 >> -> Seq Scan on assets (cost=3D0.00..936605.40 rows=3D867607 = width=3D53) >>=20 >> Filter: (((_doc #> '{floatValue}'::text[]))::double precision < >> '3'::double precision) >>=20 >>=20 >=20 > The index scan is likely expected to be more expensive than the plain > sequential scan with the LIMIT interrupting it pretty much right away > (it's expected to scan only ~0.0003% of the table. >=20 > You can probably push the database to use the index by disabling > sequential scans, i.e. >=20 > SET enable_seqscan =3D off; >=20 > and then doing the explain again. >=20 > The interesting question however is which of the plans is faster. It's > quite possible the database is making the right choice - index scans = are > not necessarily faster. >=20 >>=20 >> The version of the database, >>=20 >>=20 >>=20 >> "PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 >> 20140120 (Red Hat 4.8.2-16), 64-bit" >>=20 >>=20 >>=20 >> However, the index works for text field in JSONB. >>=20 >=20 > Well, the text field probably has different statistics, so the > sequential scan would have so scan much larger part of the table. Who > knows - you haven't shared the execution plans. >=20 >>=20 >>=20 >> Let me know if I can provide more information. >>=20 >=20 > Show us explain analyze for both queries, with both index-scan and > seq-scan (you'll have to use enable_seqscan and enable_indexscan to > force the plan choice). >=20 >=20 > regards >=20 > --=20 > Tomas Vondra http://www.2ndQuadrant.com = > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services=20= --Apple-Mail=_3310C128-C933-4755-8300-770AC5B2946B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Run those 2 EXPLAINs with seqscan off and on. See what = difference it makes!


SET= enable_seqscan =3D off;
explain analyze select id, = _doc->>'floatValue' from assets where (_doc #> = '{floatValue}'::text[])::double precision < 3.0 limit = 100;

"Limit  (cost=3D0.43..313.25 = rows=3D100 width=3D53) (actual time=3D0.092..0.236 rows=3D7 = loops=3D1)"
"  ->  Index Scan using assets_floatvalue_idx on = assets  (cost=3D0.43..2714072.57 rows=3D867607 width=3D53) (actual = time=3D0.089..0.230 rows=3D7 loops=3D1)"
"        Index Cond: = (((_doc #> '{floatValue}'::text[]))::double precision < = '3'::double precision)"
"Planning Time: 1.617 = ms"
"Execution Time: 0.276 ms"

SET= enable_seqscan =3D on;
explain analyze select id, = _doc->>'floatValue' from assets where (_doc #> = '{floatValue}'::text[])::double precision < 3.0 limit = 100;

"Limit  (cost=3D0.00..107.95 = rows=3D100 width=3D53) (actual time=3D41021.311..313501.746 rows=3D7 = loops=3D1)"
"  ->  Seq Scan on assets  = (cost=3D0.00..936605.40 rows=3D867607 width=3D53) (actual = time=3D41021.309..313501.732 rows=3D7 loops=3D1)"
"      =   Filter: (((_doc #> '{floatValue}'::text[]))::double precision = < '3'::double precision)"
"        Rows = Removed by Filter: 2602824"
"Planning Time: 0.283 = ms"
"Execution Time: 313501.777 ms"



Zhihong

On Dec 20, 2019, at 5:30 PM, = Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

On Fri, Dec 20, 2019 at = 04:52:17PM -0500, Zhihong Zhang wrote:
I have an index on JSONB fields like = this,



CREATE = INDEX float_number_index_path2

  ON= public.assets USING btree

  (((_doc #> '{floatValue}'::text[])::double = precision) ASC NULLS LAST)

  TABLESPACE pg_default;



However query doesn't use = it,



explain = select id, _doc->>'floatValue' from assets where (_doc #>
'{floatValue}'::text[])::double precision < 3.0 limit = 3;



Limit =  (cost=3D0.00..3.24 rows=3D3 width=3D53)

->  Seq Scan on assets  (cost=3D0.00..936605.40 = rows=3D867607 width=3D53)

      Filter: (((_doc #> = '{floatValue}'::text[]))::double precision <
'3'::double = precision)



The index scan is likely = expected to be more expensive than the plain
sequential scan with the LIMIT = interrupting it pretty much right away
(it's expected to scan only ~0.0003% of the table.

You can probably push the = database to use the index by disabling
sequential scans, i.e.

 SET enable_seqscan =3D off;

and then doing the explain = again.

The = interesting question however is which of the plans is faster. = It's
quite = possible the database is making the right choice - index scans = are
not = necessarily faster.


The version of the = database,



"PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc = (GCC) 4.8.2
20140120 (Red Hat 4.8.2-16), 64-bit"



However, the = index works for text field in JSONB.


Well, the text field probably has different statistics, so = the
sequential = scan would have so scan much larger part of the table. Who
knows - you haven't shared the = execution plans.



Let me = know if I can provide more information.


Show us explain analyze for both queries, with both = index-scan and
seq-scan (you'll have to use enable_seqscan and = enable_indexscan to
force the plan choice).


regards

-- Tomas Vondra =             &n= bsp;    http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training = & Services 

= --Apple-Mail=_3310C128-C933-4755-8300-770AC5B2946B--