Re: Abnormal JSON query performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: "007reader(at)gmail(dot)com" <007reader(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Abnormal JSON query performance
Date: 2018-05-14 05:18:42
Message-ID: 26540.1526275122@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Sunday, May 13, 2018, <007reader(at)gmail(dot)com> wrote:
>> My interest is in fast access to data. On a relational table, the query
>> time is about the same whether I have one or ten fields in a select
>> statement. I’d love to see the same behavior when getting multiple keys
>> from a JSON document.

> I would hazard to say this is a solid desire and one shared by many. It is
> also seemingly something that today has little or no precedent in
> PostgreSQL. The lack is not a bug.

Yeah. This has been discussed from time to time before. The problem
is how to reconcile it with PG's extensible architecture, in which these
various -> and ->> operators are independent functions that are black
boxes so far as the core code is concerned. It's very unclear how to
set up an arrangement that would let them share processing.

For the moment, you can work around it to some extent by writing out
the shared processing manually, along the lines of

select (x).this, (x).that, (x).the_other from
(select jsonb_populate_record(null::myrowtype, jsonb_column) as x
from ...) ss

where myrowtype defines the fields you want to extract.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2018-05-14 06:59:13 Re: [HACKERS] [BUGS] Bug in Physical Replication Slots (at least 9.5)?
Previous Message Pavel Stehule 2018-05-14 05:04:16 Re: Abnormal JSON query performance