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 1ilxxB-0005Rr-Q7 for pgsql-bugs@arkaria.postgresql.org; Mon, 30 Dec 2019 16:31:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ilxxA-0002s9-Ig for pgsql-bugs@arkaria.postgresql.org; Mon, 30 Dec 2019 16:31:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ilxxA-0002s0-09 for pgsql-bugs@lists.postgresql.org; Mon, 30 Dec 2019 16:31:36 +0000 Received: from mail-qk1-x736.google.com ([2607:f8b0:4864:20::736]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ilxx3-0003zs-DB for pgsql-bugs@lists.postgresql.org; Mon, 30 Dec 2019 16:31:34 +0000 Received: by mail-qk1-x736.google.com with SMTP id x1so26560898qkl.12 for ; Mon, 30 Dec 2019 08:31:29 -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=keeP22qchAOuTd/PqDinLnNEbRzkop/c59aNxHQYCKo=; b=HS/T/vn5JZDQGNr7JxsR6pVKqOFvjWYWn0gdj7HuIr6qpkv7bwKoE23kgjmSjQ0a6Z h4uGKBlayrwmsX1pqBxY9++Yur8VJLjhkXDU/T477uDB4Zd/TDdiwVQw+r50sGC6Ye8l 70GmqHNL6G+urzi5vUgYyK/xrwRnKQ6etM0Ydk+YUU7NWnGGhKWjJCWCkpqi0qhTv7Ta qpkkYVEwoghYxnI8nGirtX/IncPtesbQFCieo+DYM1dYjz1HlX/iSDTxYeH2pF28W3f4 NAqGyYDBVI7q5fFYIMRfSnIKmbZq9e3plXRl8lxS75ZhGklG1a6V1H1oo5Z0Bt4J6kQl bzQg== 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=keeP22qchAOuTd/PqDinLnNEbRzkop/c59aNxHQYCKo=; b=Gw8U5Jsdw1qBboYcfivxvJOOr7zcAnt4QLbWlDVbpxzshFFdrIBgr3qZXh/woiaVSS sqAVbb6ls8eP3D0EVMCDOzE8J9EuHWX0Iei80Ws7mEagIavheAEgC5Xi948ey7wZiq3K y+tRmQyJICZBqB1uIfJaEtikiSsoCEJhH0u2W+2wuzSGL8LferPFJi928I4ZZYYxHVcK DfBJEqFMy+Z1wi14SUQUsNKINNoLfEuTqBx5As9d/Kvy8Slw9kxIyeC8osr1M4wXM9xb RJHy2KneUgpjigmTHwtHzNIJP/Ry1+o0AyzYn17FRUxQhXJ72HnItrLjgi6sWkWOnWMT 0Pkw== X-Gm-Message-State: APjAAAWuIydT1PXAhwUrjYj4yWmsVxIsG04YpHrGUZTBe92rj3ORj4jM Dh+u8gAEsLTKb8ql0BDEMFk= X-Google-Smtp-Source: APXvYqz26sih134nxvxjBQDviBW8fXaTdYpGpB//8rL6eOjRqCNihygt2vTeTtBEx+gNzCH6W5bYfQ== X-Received: by 2002:a37:658f:: with SMTP id z137mr56591469qkb.234.1577723488584; Mon, 30 Dec 2019 08:31:28 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-105-206.washdc.fios.verizon.net. [173.79.105.206]) by smtp.gmail.com with ESMTPSA id m21sm12583453qka.117.2019.12.30.08.31.27 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 30 Dec 2019 08:31:28 -0800 (PST) From: Zhihong Zhang Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_87A4DE1A-51F6-44CA-A330-F707740E2CAE" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Mon, 30 Dec 2019 11:31:27 -0500 In-Reply-To: Cc: Tom Lane , Tomas Vondra , pgsql-bugs To: Jeff Janes References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> <167501d5bcdb$13854b20$3a8fe160$@gmail.com> <11610.1577468732@sss.pgh.pa.us> <202A204F-FE4D-414A-ABD0-B7773A0C7316@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=_87A4DE1A-51F6-44CA-A330-F707740E2CAE Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >=20 >=20 > You will have to redo the EXPLAIN (ANALYZE, BUFFERS) now that you have = stats, under both settings of enable_seqscan, and show us those. It=E2=80=99s working now! I really don=E2=80=99t know what=E2=80=99s = changed. Other than tweaking a few unrelated parameters, all I did was = bouncing the server a few times. The missing stats for the jsonb index is back also. We had the same problem on another RDS server in a different cloud also. = Let me see if I can get access there and do some comparison. Here is the explain for the same query that took minutes before, SET enable_seqscan =3D on; SET catalog_load_test=3D> explain analyze select id, _doc->>'floatValue' = from assets where (_doc #> '{floatValue}'::text[])::double precision < = 3.0 limit 100; QUERY = PLAN = --------------------------------------------------------------------------= --------------------------------------------------------------- Limit (cost=3D0.43..28.55 rows=3D6 width=3D53) (actual = time=3D0.046..0.177 rows=3D7 loops=3D1) -> Index Scan using float_number_index_path2 on assets = (cost=3D0.43..28.55 rows=3D6 width=3D53) (actual time=3D0.045..0.172 = rows=3D7 loops=3D1) Index Cond: (((_doc #> '{floatValue}'::text[]))::double = precision < '3'::double precision) Planning Time: 0.274 ms Execution Time: 0.205 ms (5 rows) SET enable_seqscan =3D on; SET catalog_load_test=3D> explain analyze select id, _doc->>'floatValue' = from assets where (_doc #> '{floatValue}'::text[])::double precision < = 3.0 limit 100; QUERY = PLAN = --------------------------------------------------------------------------= --------------------------------------------------------------- Limit (cost=3D0.43..28.55 rows=3D6 width=3D53) (actual = time=3D0.046..0.177 rows=3D7 loops=3D1) -> Index Scan using float_number_index_path2 on assets = (cost=3D0.43..28.55 rows=3D6 width=3D53) (actual time=3D0.045..0.172 = rows=3D7 loops=3D1) Index Cond: (((_doc #> '{floatValue}'::text[]))::double = precision < '3'::double precision) Planning Time: 0.274 ms Execution Time: 0.205 ms (5 rows) Thanks for all your help! I will keep digging till I find the cause. Zhihong --Apple-Mail=_87A4DE1A-51F6-44CA-A330-F707740E2CAE Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8


You will have to redo the EXPLAIN = (ANALYZE, BUFFERS) now that you have stats, under both settings of = enable_seqscan, and show us those.


It=E2=80=99s working = now! I really don=E2=80=99t know what=E2=80=99s changed. Other than = tweaking a few unrelated parameters, all I did was bouncing the server a = few times.

The missing stats for the = jsonb index is back also.

We had the = same problem on another RDS server in a different cloud also. Let me see = if I can get access there and do some comparison.

Here is the explain for the same query that took = minutes before,
SET enable_seqscan =3D on;
SET
catalog_load_test=3D> explain analyze select id, = _doc->>'floatValue' from assets where (_doc #> = '{floatValue}'::text[])::double precision < 3.0 limit 100;
= QUERY PLAN
---------------------------------------------------------------= --------------------------------------------------------------------------=
Limit (cost=3D0.43..28.55 = rows=3D6 width=3D53) (actual time=3D0.046..0.177 rows=3D7 loops=3D1)
-> Index Scan using = float_number_index_path2 on assets (cost=3D0.43..28.55 rows=3D6 = width=3D53) (actual time=3D0.045..0.172 rows=3D7 loops=3D1)
Index Cond: (((_doc = #> '{floatValue}'::text[]))::double precision < '3'::double = precision)
Planning Time: = 0.274 ms
Execution Time: = 0.205 ms
(5 = rows)

SET =
enable_seqscan =3D on;
SETcatalog_load_test=3D> = explain analyze select id, _doc->>'floatValue' from assets where = (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit = 100;
= QUERY PLAN
---------------------------------------------------------------= --------------------------------------------------------------------------=
Limit (cost=3D0.43..28.55 = rows=3D6 width=3D53) (actual time=3D0.046..0.177 rows=3D7 loops=3D1)
-> Index Scan using = float_number_index_path2 on assets (cost=3D0.43..28.55 rows=3D6 = width=3D53) (actual time=3D0.045..0.172 rows=3D7 loops=3D1)
Index Cond: (((_doc = #> '{floatValue}'::text[]))::double precision < '3'::double = precision)
Planning Time: = 0.274 ms
Execution Time: = 0.205 ms
(5 rows)


Thanks for all your help! I will keep digging till = I find the cause.

Zhihong




= --Apple-Mail=_87A4DE1A-51F6-44CA-A330-F707740E2CAE--