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 1iiQWf-0004u9-G8 for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Dec 2019 22:13:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iiQWe-0003aF-AB for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Dec 2019 22:13:36 +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 1iiQC8-00027q-9N for pgsql-bugs@lists.postgresql.org; Fri, 20 Dec 2019 21:52:24 +0000 Received: from mail-qt1-x833.google.com ([2607:f8b0:4864:20::833]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiQC5-0004xp-Dl for pgsql-bugs@lists.postgresql.org; Fri, 20 Dec 2019 21:52:23 +0000 Received: by mail-qt1-x833.google.com with SMTP id d18so6793345qtj.10 for ; Fri, 20 Dec 2019 13:52:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=p7S9k81jjOObJTqDL62aV6rhYG5t/EGPWXvikEFM3xM=; b=UEHHopZ2lwk6fZkFFCSHg68AkjDBH+rOlal2l/5VyoRwrMvTxu+Nuz13iHyANVa9zq mgpF5MfDvCW8XcFfFD/uPX19zU+a9JyekhzDEcY5W3t6qiuyEwbCZKLqRyVLTG460GXY 4997o4QQl1hiW9iiGUPrOw6csg9GPEHyl0BOgbWPSfhAMdGEcl1hfArN3QBhAM+yQE86 V8SJcTxa0uRgL3aEJoRZCvh7BGL8RCl7xa0gbJtFPgm6W4wkH1DKl2xGXenBp7tmreKU PFLB3JfSpUgdhYOmtPfhZHL9PJKNaJzzkwefnkBxtFbP7WygoyFvi1o4bEcArRkrwvDy wrow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=p7S9k81jjOObJTqDL62aV6rhYG5t/EGPWXvikEFM3xM=; b=Uqr/lwB9TdPjUd2WYlEBjVl09nB+/kO2bnNpPxrQ/loWnYXrBr+uAWrLJunjdmC12P IPmPxznzupq2Urf+/gkjo5H/ow5iH56HG/F+f/SIfi4OlTq3gL8IZV6FMjXsZCi4LVwf mqEQakiwiYMQjl1LwYsonKpbCKDE62j2VNz8zuP4JfALm1ENsW4lH2XsTuVgWPom42rR Ynd0RKVvukUqEPtaD8WqxBMRoBBFG1oTxxkXxM0UXDPbyLM2vN5o/Yy9DCXPBAKGQVpG 9CwrWaSuYI8f9HgduSRHLiDTTaxLhk7qAKkkA3B4G8mZHjpfKKzHkH7fYi/7PMCtw5h9 X3Zw== X-Gm-Message-State: APjAAAUpxUQq4wT2u85vRqPEGllF7El7Dj2En/HQ6jGOFvzr8cBhyG5G 6HUYvDyPqPnXTLzznM6zT4c/oLs0 X-Google-Smtp-Source: APXvYqyMUNxlDm0OOr/jYCLrd4ZXK6sofm2InOH5wdKtRwloQORb2y0ying3WsADvfp+sqpVws+wSg== X-Received: by 2002:ac8:32ec:: with SMTP id a41mr13804287qtb.235.1576878739160; Fri, 20 Dec 2019 13:52:19 -0800 (PST) Received: from zhangzdt (static-71-246-230-26.washdc.fios.verizon.net. [71.246.230.26]) by smtp.gmail.com with ESMTPSA id u24sm3304444qkm.40.2019.12.20.13.52.18 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 20 Dec 2019 13:52:18 -0800 (PST) From: "Zhihong Zhang" To: Subject: Indexing on JSONB field not working Date: Fri, 20 Dec 2019 16:52:17 -0500 Message-ID: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0CF1_01D5B755.D710E710" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AdW3fh6QS+SYHJvJR+2d83QXE2Mw6Q== Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0CF1_01D5B755.D710E710 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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=0.00..3.24 rows=3 width=53) -> Seq Scan on assets (cost=0.00..936605.40 rows=867607 width=53) Filter: (((_doc #> '{floatValue}'::text[]))::double precision < '3'::double precision) 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. Let me know if I can provide more information. Zhihong Zhang zhihong@gmail.com ------=_NextPart_000_0CF1_01D5B755.D710E710 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 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.

 

Let me know = if I can provide more information.

 

Zhihong = Zhang

zhihong@gmail.com

 

 

 

------=_NextPart_000_0CF1_01D5B755.D710E710--