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 1iiT3A-0002rH-28 for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 00:55: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 1iiT38-0003Xq-G1 for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 00:55: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 1iiSnt-0008NV-Ub for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 00:39:34 +0000 Received: from mail-qv1-xf34.google.com ([2607:f8b0:4864:20::f34]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiSnr-0006Ec-Lc for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 00:39:33 +0000 Received: by mail-qv1-xf34.google.com with SMTP id o18so4330988qvf.1 for ; Fri, 20 Dec 2019 16:39:31 -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=LP1MwWau2R3lLJfeZ+l9d9Hb/cukJh8iUAoLmQHaCUA=; b=q1RceQbTJhXjz6y3vmTux7+MjYR0GMzhr1bk8wIKJizgjDoEyAtjnWA4KXBsr5GiPR Jr1FVUB6Add9vyymk/U6lNEtJpsycxnuI/5cIs1BMoiFNbriD3k33IQNvgZ395vf9+Us zVMNbDO8FZZF7cnYwolVzFn9cEDz/jFRuwVyXWOhkKO5AWlu8WfeZJ20I8KQkHNYb2PU xl5rZ0GaH96v8Bw7B/Xtz4G878rjUgNI3EbUijp0GOLsCBechcjiUswvlZEiiTp6YDZ4 rlpNrfgw9GrDWO2tSEa7g564txc4Yna1Yw+9NfcuE4uyg/uTcFmuVFHbkpTOD75j8OYD HQnA== 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=LP1MwWau2R3lLJfeZ+l9d9Hb/cukJh8iUAoLmQHaCUA=; b=WW+oVlbRlmCMFFI7fAejChHwszfC+OtqRN7h7dHkd/FrtXz8FkPzo0AIdnwwvybnmu 0rwzMSwavx5cG6Vt94oLQ97oEmnxeK8S+avjdw92aeXZLivKSnD9UL5gZV97ruvmjzdb 0Jsd1/A9xeLmiU9LV9EfnFUVDSzjcyZsqb9y0HRVEEDZyCkM/bj7Cr/Qa3sXpPqnVjYc vzr9KJLeblB8e1YIOcw1ICEYzpdENyWxCpHGoxWF2Xna85hKLFA4udMqlDVUU5sh3cQz HzDVl1fcYFoXWOvmVnV5gSDw9W/TJe/kr9fwuG3gLtKMhXbRtURFHimxwZ0wiSr21Bxs Brwg== X-Gm-Message-State: APjAAAXCBREw1u6WjRjGwWUk7mLnutrORfVGB5M/Y7bL93950vJcQWns qPBL8NBIVZg+UHXfz+H8Zdc= X-Google-Smtp-Source: APXvYqxYwkw7wBfivsMSj/Jc3fjr16cHt5Rwye6BZFE5fX192fadrdQoKpwk8sjUvSWBBw1OvAycuA== X-Received: by 2002:a0c:bd2c:: with SMTP id m44mr407899qvg.248.1576888769619; Fri, 20 Dec 2019 16:39:29 -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 x24sm3715422qtx.1.2019.12.20.16.39.28 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 20 Dec 2019 16:39:29 -0800 (PST) From: Zhihong Zhang Message-Id: <9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_AF8DEC51-A78D-4946-ACA8-C34D8B456425" 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 19:39:28 -0500 In-Reply-To: Cc: pgsql-bugs To: Jeff Janes References: <0cf001d5b77f$bfe47e10$3fad7a30$@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=_AF8DEC51-A78D-4946-ACA8-C34D8B456425 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 A few clarifications, 1. The index is very effective. If I treat it as text, the index works. = With index, the result returns in 1 second. Otherwise, it=E2=80=99s 5 = minutes. 2. Removing limit doesn=E2=80=99t change the behavior. 3. I ran Analyze multiple times after indexing. Zhihong > On Dec 20, 2019, at 5:57 PM, Jeff Janes wrote: >=20 > On Fri, Dec 20, 2019 at 5:12 PM 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=E2=80=99t use it, >=20 >=20 > Did you analyze the table after building the index? Expression = indexes have their own statistics, but they don't get populated until = the table is analyzed. >=20 > Cheers, >=20 > Jeff --Apple-Mail=_AF8DEC51-A78D-4946-ACA8-C34D8B456425 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 A = few clarifications,

1.= The index is very effective. If I treat it as text, the index works. = With index, the result returns in 1 second. Otherwise, it=E2=80=99s 5 = minutes.
2. Removing limit doesn=E2=80=99t change = the behavior.
3. I ran Analyze multiple times after = indexing.

Zhihong


On Dec = 20, 2019, at 5:57 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Fri, Dec 20, 2019 at 5:12 PM Zhihong Zhang = <zhihong@gmail.com> 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=E2=80=99t use it,


Did you analyze the = table after building the index?  Expression indexes have their own = statistics, but they don't get populated until the table is = analyzed.

Cheers,

Jeff

= --Apple-Mail=_AF8DEC51-A78D-4946-ACA8-C34D8B456425--