From: Zhihong Zhang <zhihong@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Indexing on JSONB field not working
Date: Thu, 2 Jan 2020 16:49:01 -0500
Message-ID: <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com> (raw)
In-Reply-To: <A69DA80B-B5A4-4594-8178-55FB5761B386@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
<20191220223005.f6w4ff4kdbgtanpz@development>
<E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com>
<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>
<CAMkU=1y+D8SyZ+MOQAsJN_4HRm5QaLWFgKe2MyXbkbz0d5fmrA@mail.gmail.com>
<CE20A918-FDF4-4D43-A969-6CBB70B69164@gmail.com>
<CAMkU=1waicvb_FODcbJeZ8sO4EqTu7wZX4rQKu88AKxoCy97Zw@mail.gmail.com>
<A69DA80B-B5A4-4594-8178-55FB5761B386@gmail.com>
I think the root cause of my problem is that the “CREATE INDEX” with expression wouldn’t generate stats automatically. Running ‘Analyze’ manually solves the problem in most cases but sometimes I have to restart the server.
So I like to report 2 bugs,
1. “CREATE INDEX” 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’t 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’s working now! I really don’t know what’s 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 = on;
> SET
> catalog_load_test=> explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.43..28.55 rows=6 width=53) (actual time=0.046..0.177 rows=7 loops=1)
> -> Index Scan using float_number_index_path2 on assets (cost=0.43..28.55 rows=6 width=53) (actual time=0.045..0.172 rows=7 loops=1)
> 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 = on;
> SET
> catalog_load_test=> explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.43..28.55 rows=6 width=53) (actual time=0.046..0.177 rows=7 loops=1)
> -> Index Scan using float_number_index_path2 on assets (cost=0.43..28.55 rows=6 width=53) (actual time=0.045..0.172 rows=7 loops=1)
> 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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: zhihong@gmail.com, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, tomas.vondra@2ndquadrant.com
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com> <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> <CAMkU=1y+D8SyZ+MOQAsJN_4HRm5QaLWFgKe2MyXbkbz0d5fmrA@mail.gmail.com> <CE20A918-FDF4-4D43-A969-6CBB70B69164@gmail.com> <CAMkU=1waicvb_FODcbJeZ8sO4EqTu7wZX4rQKu88AKxoCy97Zw@mail.gmail.com> <A69DA80B-B5A4-4594-8178-55FB5761B386@gmail.com> <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox