query gets very slow when :jsonb ?& operator is used

[davidjesse091]

I have also asked this question on Stackoverflow and DBA stack exchange with no 
answer. It's a fairly long post, so I will post a link to it, as on 
Stackoverflow it is formatted nicely 
https://stackoverflow.com/questions/52212878/query-gets-very-slow-when-jsonb-operator-is-used

Any idea why my query slows down so much when I add 
account.residence_details::jsonb ?& array['city', 'state', 'streetName'] ?

Re: query gets very slow when :jsonb ?& operator is used

[Jeff Janes]

On Thu, Sep 6, 2018 at 7:52 PM  wrote:

> I have also asked this question on Stackoverflow and DBA stack exchange
> with no answer. It's a fairly long post, so I will post a link to it, as on
> Stackoverflow it is formatted nicely
>
>
> https://stackoverflow.com/questions/52212878/query-gets-very-slow-when-jsonb-operator-is-used
>
> Any idea why my query slows down so much when I add 
> account.residence_details::jsonb
> ?& array['city', 'state', 'streetName'] ?
>

The planner has no insight into what fraction of rows will satisfy the ?&
condition, and falls back on the assumption that  very few will.  This is
(apparently) a very bad assumption, and causes it choose a bad plan.

Rewriting the `phone_number.account_id IN (subquery)` into an exists query
might help.

Cheers,

Jeff

Re: Performance difference in accessing differrent columns in a Postgres Table

[Dinesh Kumar]

Ok, will do that. Thanks a lot.

On Wed, Sep 5, 2018 at 9:37 PM Jeff Janes  wrote:

>
>
> On Wed, Sep 5, 2018 at 12:00 PM Jeff Janes  wrote:
>
>> On Wed, Sep 5, 2018 at 12:21 AM Dinesh Kumar  wrote:
>>
>>> Hi All,
>>> I was wondering whether the case is solved or still continuing. As a
>>> Postgres newbie, I can't understand any of the terms (JIT, tuple
>>> deformation) as you mentioned above. Please anyone let me know , what is
>>> the current scenario.
>>>
>>>
>> JIT is a just-in-time compilation, which will be new in v11.  Tuple
>> deforming is how you get the row from the on-disk format to the in-memory
>> format.
>>
>> Some people see small improvements in tuple deforming using JIT in your
>> situation, some see large decreases, depending on settings and apparently
>> on hardware.  But regardless, JIT is not going to reduce your particular
>> use case (many nullable and actually null columns, referencing a
>> high-numbered column) down to being constant-time operation in the number
>> of preceding columns.  Maybe JIT will reduce the penalty for accessing a
>> high-numbered column by 30%, but won't reduce the penalty by 30 fold.  Put
>> your NOT NULL columns first and then most frequently accessed NULLable
>> columns right after them, if you can.
>>
>
> Correction: NOT NULL columns with fixed width types first.  Then of the
> columns which are either nullable or variable width types, put the most
> frequently accessed earlier.
>
>