Обсуждение: [GENERAL] Index using in jsonb query
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;
Thanks
select field1,field2 from datatable where (jsonfield#>>'{path1,path2}')::numeric < 1000;
Thanks
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
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.
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
On Sun, Mar 12, 2017 at 9:50 AM, SuperCiccio <sc_030268@yahoo.it> wrote: > 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. Have you checked why it does not use the index? ( how many rows are returned, stimates etc.. ). Maybe it is not using it because it is faster ( than using it, index fetches are slower than sequential fetches ( for the same number of rows ), so for some queries it is better to not use the index ) ( specially if you are using them for small test tables, i.e., for a single page table nothing beats a sequential scan ). Francisco Olarte.