From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | SuperCiccio <sc_030268(at)yahoo(dot)it> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Index using in jsonb query |
Date: | 2017-03-12 10:13:07 |
Message-ID: | CA+bJJbxTueGFg-e7aK_2w3T3VMu28fd_PudKLu9mYvWKYZFEDA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Mar 12, 2017 at 9:50 AM, SuperCiccio <sc_030268(at)yahoo(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-03-12 15:11:44 | Re: How to define the limit length for numeric type? |
Previous Message | SuperCiccio | 2017-03-12 08:50:26 | Re: Index using in jsonb query |