I would expect that R2 vs R3 would be negligible but perhaps gist works much better and would be an improvement. When you are down to 7ms already, I wouldn't hope for any big change. I assume you used btree for the multi-column index on R2 range_first, range_last but am not familiar with gist on range vs btree on two int columns.
It seems a little odd to have a jsonb value to hold multiple range values. A range is already a complex type so separating out into the association table like R3 would make sense to me.
Michael Lewis
On Tue, Feb 19, 2019 at 9:34 AM Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:
I dont have any indexes on R (the table with the jsonb column). I was asking if I can create any that can increase this query`s performance.If I understood you correctly I have 3 options right now :1)R, without indexes2)R2 with an index on first and last3)R3 that should contain a single range column (type int4range) with gist index on it.In aspect of performance, R<R2<? R3בתאריך יום ג׳, 19 בפבר׳ 2019 ב-18:28 מאת Michael Lewis <mlewis@xxxxxxxxxxx>:Is your JSON data getting toasted? I wouldn't assume so if it is remaining small but something to check. Regardless, if an index exists and isn't being used, then that would be the primary concern. You didn't share what the definition of the index on R.data is... what do you already have?You have an array of ranges stored as the value of key "ranges" in jsonb field data. If you created a table like R2, but with a single "range" column that is int4range type, then I would expect that you could add a GiST and then use overlaps &&, or another operator. I would not expect that you could index (unnest data->>'ranges' for instance) to get the separated out range values.Michael LewisOn Tue, Feb 19, 2019 at 8:59 AM Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:Hi,I have a table with json col : R(object int, data jsonb).Example for content :object | data----------------+---------------------------------------50 | {"ranges": [[1, 1]]}51 | {"ranges": [[5, 700],[1,5],[9,10}52 | {"ranges": [[4, 200],[2,4],[3,4]]}53 | {"ranges": [[2, 2]]}54 | {"ranges": [[5, 10]]}Now I tried to query for all the objects that contains a specific range, for example [2,2] :explain analyze SELECT *FROM R dWHERE EXISTS (SELECT FROM jsonb_array_elements(R.data -> 'ranges') rngWHERE (rng->>0)::bigint <= 2 and (rng->>1)::bigint >= 2);I saw that the gin index isnt suitable for this type of comparison. However, I saw that the gist index is suitable to handle ranges. Any idea of I can implement a gist index here ?In addition, I saved the same data in relational table R2(object,range_first,range_last).The previous data in this format :object range_first range_last50 1 151 5 70051 1 551 9 10i compared the first query with :explain analyze select * from R2 where range_first <=2 and range_last >= 2; (I have an index on range_first,range_last that is used)The query on the jsonb column was 100x slower (700 m/s vs 7m/s). The question is, Am I missing an index or the jsonb datatype isnt suitable for this structure of data. The R2 table contains 500K records while the R table contains about 200K records.
