From: | SuperCiccio <sc_030268(at)yahoo(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index using in jsonb query |
Date: | 2017-03-12 08:50:26 |
Message-ID: | 6fe09d5a-fa8c-419d-5005-e748980f4408@yahoo.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
This is a kind of document, there are many other kinds, with different
json structure. So, a general index would be better.
But even if I create a specific index
CREATE INDEX on datatable (((jsonfield#>>'{path1,path2}')::numeric));
it isn't used in such a query; it is used in this query:
select field1,field2 from datatable where
(jsonfield#>>'{path1,path2}')::numeric = 1000;
Definitely, I didn't find a way to optimize a query that checks a range
in a json subfield.
> On 3/11/2017 3:55 PM, SuperCiccio wrote:
>> Is there a way to make the following query use some sort of indexing?
>>
>> select field1,field2 from datatable where
>> (jsonfield#>>'{path1,path2}')::numeric < 1000;
>
> if the '{path1,path2}' part is constant, I'd think you could create an
> index on (jsonfield #>> '{path1,path2}')::numeric
>
>
> --
> john r pierce, recycling bits in santa cruz
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2017-03-12 10:13:07 | Re: Index using in jsonb query |
Previous Message | vod vos | 2017-03-12 08:33:59 | Re: How to define the limit length for numeric type? |