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 1ikvWN-0006F7-OW for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 19:43:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ikvWM-0002sQ-4M for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 19:43:38 +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 1ikvWL-0002sF-Q9 for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 19:43:37 +0000 Received: from mail-qv1-xf44.google.com ([2607:f8b0:4864:20::f44]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ikvWH-0002g6-VY for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 19:43:37 +0000 Received: by mail-qv1-xf44.google.com with SMTP id f16so10376126qvi.4 for ; Fri, 27 Dec 2019 11:43:33 -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=JITv668yODVZ9eXY6dsywB27NMS0pi+ga20ZA9rEqog=; b=AwmWnzV/wC+6dlg4ko6RHpQ+xDJxLD+3e26USbbNzej/+C+T+2uKPFhNYSVBRlyJMn TqajlNdDiypcNdCLg0ikhFsQC7oI343hOKEppyiCQKu089nzzR/7h2/11IGJ/ktVf+MY AM2naCvVZfTc4Ia2AF0UFjyFgRnxuU6IQOdDJPEY5a2J6gSU80rA9aQvedfjOEz5tXWc F4EKxwrKbAaWDbrDKsQrtpxcPYzyJFGGkVTPTnAxhFMZfcGl0D1K3Ui1uZK6r2RRAER7 4BckAvqFxxzWNA/3rW6AG93bx72t70AYNMVxiKk8M0dVXJ01yC/6d2FR7kAC4EOIrtRj clng== 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=JITv668yODVZ9eXY6dsywB27NMS0pi+ga20ZA9rEqog=; b=VQAiVtKQRobzkakkiCGv3x1fESapqbkQ7ZxIy8qLO2a8B8h+FaEkYV95TATLKHE+Ob iIZ0kvx8Xs50FgM8BqjSDsOqFIOW6KcZ87T2ZZvcRRdVYWH3hh9MWt3TNrYMWvZfwlzu YvhxooeyLmzZkKOB8OlYD3cBafSRVHcq0PSB1/Zj7H4kMR1FmyJXOVLGixlv6MR0japA HrQQQwoukq2MesNDggLiBK+XdbIuoZe9jGYn2Nne4eD2KWE16Pymm7LEab+X/GjI2o8o zgpeNDCApH+vZDLSFt/Z84AkYqXovUKwQZZHSXnQKaKIYvaWSwab6gNxJ3Pod94rVGZd /UVA== X-Gm-Message-State: APjAAAVR6PpCv/7kSi+L1fBCong4UlOPfj7wHBzWoCMUj+Om1aNFGY0J lydjOSVVF+Kh/NhF4dDuANA= X-Google-Smtp-Source: APXvYqzjak38l/68aS86chnWgPawrl1tk7vsdKjoOe5KW3qm0FEsWx+hvK4OtDr0bShvBLV8f5lEhw== X-Received: by 2002:a05:6214:287:: with SMTP id l7mr31664069qvv.142.1577475811051; Fri, 27 Dec 2019 11:43:31 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-120-3.washdc.fios.verizon.net. [173.79.120.3]) by smtp.gmail.com with ESMTPSA id l184sm9943172qkc.107.2019.12.27.11.43.29 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 27 Dec 2019 11:43:30 -0800 (PST) From: Zhihong Zhang Message-Id: <31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_40605DAD-EFDC-4895-9A42-B0BB7750F73A" 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, 27 Dec 2019 14:43:29 -0500 In-Reply-To: Cc: Tomas Vondra , pgsql-bugs To: Jeff Janes References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <83E6A3F7-B1B4-4AD6-905A-BE11D244D35A@gmail.com> 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=_40605DAD-EFDC-4895-9A42-B0BB7750F73A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Dec 27, 2019, at 12:45 PM, Jeff Janes wrote: >=20 > Can you do `pg_dump -s -t assets ...` to get a full description of = the table? If you don't want to share the full description, make sure = whatever simplifications you do to it don't prevent the problem from = reproducing. >=20 I created a new table =E2=80=98assets_copy=E2=80=99 with only 3 columns. = I can still reproduce the problem. The pg_dump output is attached. Thanks! Zhihong -- -- PostgreSQL database dump -- -- Dumped from database version 11.4 -- Dumped by pg_dump version 11.6 SET statement_timeout =3D 0; SET lock_timeout =3D 0; SET idle_in_transaction_session_timeout =3D 0; SET client_encoding =3D 'UTF8'; SET standard_conforming_strings =3D on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies =3D false; SET xmloption =3D content; SET client_min_messages =3D warning; SET row_security =3D off; SET default_tablespace =3D ''; SET default_with_oids =3D false; -- -- Name: assets_copy; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.assets_copy ( id character(20), _doc jsonb, floatvalue double precision ); ALTER TABLE public.assets_copy OWNER TO postgres; -- -- Name: column_float_value_idx; Type: INDEX; Schema: public; Owner: = postgres -- CREATE INDEX column_float_value_idx ON public.assets_copy USING btree = (floatvalue); -- -- Name: jsonb_float_value_idx; Type: INDEX; Schema: public; Owner: = postgres -- CREATE INDEX jsonb_float_value_idx ON public.assets_copy USING btree = ((((_doc #> '{floatValue}'::text[]))::double precision)); -- -- PostgreSQL database dump complete -- --Apple-Mail=_40605DAD-EFDC-4895-9A42-B0BB7750F73A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Dec 27, 2019, at 12:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

Can = you do  `pg_dump -s -t assets ...` to get a full description of the = table?  If you don't want to share the full description, make sure = whatever simplifications you do to it don't prevent the problem from = reproducing.


I created a new table =E2=80=98assets_copy=E2=80= =99 with only 3 columns. I can still reproduce the problem. The pg_dump = output is attached.

Thanks!

Zhihong

--
-- PostgreSQL database dump
---- Dumped from database version = 11.4
-- Dumped by pg_dump = version 11.6SET statement_timeout =3D 0;
SET lock_timeout =3D 0;
SET = idle_in_transaction_session_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET = standard_conforming_strings =3D on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies =3D = false;
SET xmloption =3D = content;
SET = client_min_messages =3D warning;
SET row_security =3D off;SET default_tablespace =3D = '';SET = default_with_oids =3D false;--
-- Name: assets_copy; Type: TABLE; Schema: public; = Owner: postgres
--CREATE TABLE public.assets_copy (
id character(20),
_doc jsonb,
floatvalue double precision
);ALTER TABLE public.assets_copy = OWNER TO postgres;--
-- Name: column_float_value_idx; Type: INDEX; = Schema: public; Owner: postgres
--CREATE INDEX = column_float_value_idx ON public.assets_copy USING btree = (floatvalue);--
-- Name: jsonb_float_value_idx; Type: INDEX; = Schema: public; Owner: postgres
--CREATE INDEX = jsonb_float_value_idx ON public.assets_copy USING btree ((((_doc #> = '{floatValue}'::text[]))::double precision));--
-- PostgreSQL database dump complete
--

= --Apple-Mail=_40605DAD-EFDC-4895-9A42-B0BB7750F73A--