pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Zhihong Zhang <zhihong@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Indexing on JSONB field not working
Date: Fri, 27 Dec 2019 16:21:09 -0500
Message-ID: <3680.1577481669@sss.pgh.pa.us> (raw)
In-Reply-To: <31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
	<20191220223005.f6w4ff4kdbgtanpz@development>
	<E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com>
	<CAMkU=1z8X1s0WChsgKU7SN8ngrLKHt38UhAqKn-nGTf+xbQ9zg@mail.gmail.com>
	<E0F0CF2B-46DB-425C-9338-0FA59561B6B6@gmail.com>
	<CAMkU=1w8UyLY1e4N6m5nB9CmG1wjizqFS--Lmi3GZ4j1+JUP3Q@mail.gmail.com>
	<83E6A3F7-B1B4-4AD6-905A-BE11D244D35A@gmail.com>
	<CAMkU=1z4do7EF5K4Kz2rjy8R_ELnGEket-TWwa=YwjMwMb_RuA@mail.gmail.com>
	<31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com>

Zhihong Zhang <zhihong@gmail.com> writes:
>> 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 ‘assets_copy’ with only 3 columns. I can still reproduce the problem. The pg_dump output is attached.

I'm a little suspicious of this because it says the table owner is
"postgres", but you say you're not superuser.  Does RDS create a
non-superuser "postgres" account?

This is odd too:

> -- Dumped from database version 11.4
> -- Dumped by pg_dump version 11.6

But anyway, I loaded this dump into v11 as a non-superuser, and
inserted some data, specifically

insert into assets_copy select 'id', ('{"floatValue": ' || random()*1e6 || ', "junk": "z"}')::jsonb, random()*1e6 from generate_series(1,100000) x;

and I find that either auto-analyze or a manual ANALYZE will create
stats that I can see in pg_stats and that lead to sane planner
estimates.

Now, I'm testing with a recent 11-branch build, so it's theoretically
possible that some post-11.4 bug fix has changed the behavior, but
I've looked through the git logs quite carefully and I see nothing
that would have affected a query referencing the table directly.
(If you were going through a view or an inheritance/partitioning
parent, that might be a different story.)

It seems like the only remaining theory is that the RDS version
of Postgres differs from community Postgres in some relevant way.
Even that theory has problems explaining why Jeff couldn't duplicate
the behavior on his RDS instance, but we're really out of reasons for
this not to work.  Perhaps it's time for you to take up this question
with RDS support.

			regards, tom lane





view thread (43+ messages)  latest in thread

Message-ID: <3680.1577481669@sss.pgh.pa.us>
Permalink:  ../3680.1577481669@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3680.1577481669@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, zhihong@gmail.com, jeff.janes@gmail.com, tomas.vondra@2ndquadrant.com
  Subject: Re: Indexing on JSONB field not working
  In-Reply-To: <3680.1577481669@sss.pgh.pa.us>
  References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com> <CAMkU=1z8X1s0WChsgKU7SN8ngrLKHt38UhAqKn-nGTf+xbQ9zg@mail.gmail.com> <E0F0CF2B-46DB-425C-9338-0FA59561B6B6@gmail.com> <CAMkU=1w8UyLY1e4N6m5nB9CmG1wjizqFS--Lmi3GZ4j1+JUP3Q@mail.gmail.com> <83E6A3F7-B1B4-4AD6-905A-BE11D244D35A@gmail.com> <CAMkU=1z4do7EF5K4Kz2rjy8R_ELnGEket-TWwa=YwjMwMb_RuA@mail.gmail.com> <31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com> <3680.1577481669@sss.pgh.pa.us>

* 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