11

I have a table table with a JSONB field data, which contains a variable-length array, e.g.

{"label": "xyz", "items": [ ... ]}

I created an index on the length of the "items" element:

CREATE INDEX n_items ON table ( JSONB_ARRAY_LENGTH(data->'items') )

but when I filter, I still get a sequential scan when I try to filter on it:

EXPLAIN ANALYZE SELECT COUNT(*) FROM table WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=2565655.67..2565655.68 rows=1 width=8)
   ->  Seq Scan on table (cost=0.00..2535256.19 rows=12159794 width=8)
         Filter: (jsonb_array_length((table.data -> 'items'::text)) = 2)
 Planning time: 0.121 ms
 Execution time: 482891.694 ms

That's about 8 minutes to filter! Did I do something wrong here, or is this the consequence of PostgreSQL not keeping statistics on JSON(B) objects? It should be possible to flatten out this data column, but I'd like to be sure that's what I need to do before I start working on it.

edit: these array lengths do not vary much. There are only 4 distinct values in the data currently, and I don't expect to have many more. Is the index just not very useful in this case, or can I improve filtering some other way?

Improve this question

1 Answer 1

Reset to default
17

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will be able to tell if it works).

Improve this answer
6
  • The selectivity is in fact low. In this case does the index not actually help filtering performance on queries other than counts?
    – shadowtalker
    Commented Sep 16, 2016 at 14:53
  • @ssdecontrol Well, it depends - there is a chance it won't help much, unless the same two columns are involved.
    – András Váczi
    Commented Sep 16, 2016 at 15:12
  • Let me rephrase: is an index on a low-selectivity column not generally useful?
    – shadowtalker
    Commented Sep 16, 2016 at 16:22
  • 1
    @shadowtalker, it depends, the main question is: will my query return too few records if I use this column in where? If yes then an index makes sense in that column. So if you have 3 statuses on you invoice.status column and open status will return only one record then it will be useful to create an index for that column, if you want closed status that return 95% table records then index doesn't makes sense. Hope I was clear. Reference
    – deFreitas
    Commented May 14, 2018 at 14:35
  • 2
    @deFreitas let me add that in this case it makes sense to create a partial index, with the rare values specified in the WHERE clause of CREATE INDEX. And if the rare cases are very rare, some tweaking of the statistics settings might also be necessary.
    – András Váczi
    Commented May 14, 2018 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.