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 1in8L6-0005eF-Hp for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 21:49:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1in8L5-0007sz-8g for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 21:49:07 +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 1in8L4-0007o6-Mi for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 21:49:07 +0000 Received: from mail-qt1-x836.google.com ([2607:f8b0:4864:20::836]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1in8L1-0006Kj-DC for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 21:49:05 +0000 Received: by mail-qt1-x836.google.com with SMTP id j5so35591367qtq.9 for ; Thu, 02 Jan 2020 13:49:03 -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=KeHXmteS5hebvrLsHLDYGQQmMUlQJjBpTRs1PQYBsyw=; b=IlJMljpttNshYE1Q77gsTHlIG3jMRx2wHrhu7P3vSgaeFaOqaBTA8S3r0H2E65yOdv 5FnpmA8kPUzF8ELTyq+xMOvnTTRlAQmoU+9ckdumpFzSyXcWL6VtgRMTZUILzzhAKVBq vD5InN2CCXOGYjXojQzFFHdJjgje1pZxy7y+U8ZGK9NzfWYpOnTHR1jLz/7+y4b5usOZ 3bcQw5pABvJaq/cePmRBjHJw2wcRGEMcbKU2fD7glPRBbCRrDKWibgQJhN14JISSreRa 67ax/U5imVJ19fwND31zcjPjTZ6Zsj+bNxR7JKS4LjefhjjswI7M5bKQX2Oc7ZLRK3BJ JtiA== 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=KeHXmteS5hebvrLsHLDYGQQmMUlQJjBpTRs1PQYBsyw=; b=mpqymFikFZvasbc9wWiB2aD/lIEkVAi4or0tp37Unizc+hOc9g85nE9NuAwynAzZkM TkDdqpf3Da3nxwqvZbzZNIYN8beZmNJQgubzOPqA8mNba1vTuB9/pkSKUg6LDEkJ9F+Q X4sK8jnrfLZQmg4EhLliXBVEqtkTQQSpqXDKPrrOqXtMWq4X/mQQ45p8iQ/4OWxLtgCT vLoTatB7yFvWCv0Zq90SM9EvPBYYKjSW+dMDJK9CpsAtotYVRKZx0ZTiakUe3mcnSCiw Zf5wjfsPQCdjZWnVA608ZlCea4xDhSZ4XddYNqThJa9HYYHmS73mTfJZi0PPCtOrfVRf YyGw== X-Gm-Message-State: APjAAAWig9lArn4absWqFxr6wwvo4BewKjwF8C2SVyis0WpdYsogfJyd FkHN4CA+8CsQIN1+fuRvG8c= X-Google-Smtp-Source: APXvYqzDihcFwN0P2wd3UHp/fGcuW22L3oBX66337tGuCHm3vb+ks3r69gk5tAgkUoISUQ07+Ntmag== X-Received: by 2002:ac8:7088:: with SMTP id y8mr53799829qto.325.1578001742643; Thu, 02 Jan 2020 13:49:02 -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 r6sm17145327qtm.63.2020.01.02.13.49.01 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Jan 2020 13:49:02 -0800 (PST) From: Zhihong Zhang Message-Id: <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_96F9DD58-EFB5-4A13-AA27-BCFB70F6CB9E" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Thu, 2 Jan 2020 16:49:01 -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=_96F9DD58-EFB5-4A13-AA27-BCFB70F6CB9E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I think the root cause of my problem is that the =E2=80=9CCREATE = INDEX=E2=80=9D with expression wouldn=E2=80=99t generate stats = automatically. Running =E2=80=98Analyze=E2=80=99 manually solves the = problem in most cases but sometimes I have to restart the server. So I like to report 2 bugs, 1. =E2=80=9CCREATE INDEX=E2=80=9D with expression should generate stats = automatically.=20 2. Auto-analyze should generate stats if it is not available, regardless = of the number of records changed. We have a large JSONB column. We don=E2=80=99t really know which fields = are used by customer so we create indexes for those fields on demand = when there is enough usage but it never worked. We were on the brink of = switching to another database. Now with those findings, I can implement = some workaround. Thanks for your help! Zhihong > On Dec 30, 2019, at 11:31 AM, Zhihong Zhang wrote: >=20 >>=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. >=20 >=20 > 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. >=20 > The missing stats for the jsonb index is back also. >=20 > 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. >=20 > 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) >=20 > 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) >=20 >=20 > Thanks for all your help! I will keep digging till I find the cause. >=20 > Zhihong >=20 >=20 >=20 >=20 --Apple-Mail=_96F9DD58-EFB5-4A13-AA27-BCFB70F6CB9E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I = think the root cause of my problem is that the =E2=80=9CCREATE INDEX=E2=80= =9D with expression wouldn=E2=80=99t generate stats  automatically. = Running =E2=80=98Analyze=E2=80=99 manually solves the problem in most = cases but sometimes I have to restart the server.

So I like to report 2 bugs,

1. =E2=80=9CCREATE = INDEX=E2=80=9D with expression should generate stats = automatically. 
2. Auto-analyze should = generate stats if it is not available, regardless of the number of = records changed.

We have a large JSONB column. We don=E2=80=99t really know = which fields are used by customer so we create indexes for those fields = on demand when there is enough usage but it never worked. We were on the = brink of switching to another database. Now with those findings, I can = implement some workaround. Thanks for your help!

Zhihong


On Dec 30, 2019, at 11:31 AM, Zhihong Zhang <zhihong@gmail.com> = wrote:



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=_96F9DD58-EFB5-4A13-AA27-BCFB70F6CB9E--