On 3/17/19 1:14 PM, Alexander Korotkov wrote:
> On Sun, Mar 17, 2019 at 8:06 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>> On 3/17/19 1:02 PM, Alexander Korotkov wrote:
>>>
>>> Thank you for the explanation. Is it jsonb_ops or jsonb_path_ops?
>>
>> I just used "USING gin(col)" so jsonb_ops.
>
> I see. So, jsonb_ops extracts from this query only existence of
> .length key. And I can bet it exists in all (or almost all) the
> documents. Thus, optimizer thinks index might be useful, while it's
> useless. There is not much can be done while we don't have statistics
> for jsonb (and access to it from GIN extract_query). So, for now we
> can just refuse from extracting only keys from jsonpath in jsonb_ops.
> But I think it would be better to just document this issue. In future
> we should improve that with statistics.
That seems to make sense, especially given how I've typically stored
JSON documents in PostgreSQL. It sounds like this particular problem
would be solved appropriately with JSONB statistics.
Thanks,
Jonathan