jsonb gin index not being used in postgresql

Question

I create the following indexes on jsonb columns in my table:

CREATE INDEX idx_gin_accounts ON t1 USING GIN (accounts jsonb_path_ops);
CREATE INDEX idx_gin_stocks ON t1 USING GIN (stocks jsonb_path_ops);

CREATE INDEX idx_gin_stocks_value ON t1 USING GIN ((stocks-> 'value'));
CREATE INDEX idx_gin_stocks_type ON t1 USING GIN ((stocks-> 'type'));

My query is like this:

SELECT  
    t.accounts ->> 'name'                             as account_name
    //other columns
FROM t1 t
         left join lateral jsonb_array_elements(t.accounts) a(accounts)
                   on 1 = 1 and a.accounts @> '{"role": "ADVISOR"}'
         left join lateral jsonb_array_elements(t1.stocks) s(stocks)
                   on 1 = 1 and s.stocks @> '{"type": "RIC"}'
WHERE (s.stocks -> 'value' ? 'XXX')

When I analyse with EXPLAIN ANALYSE I do not see these indexes being used in the query plan.

Should different indexes be created? Or How can I use these ones to speed up the search?

Say When I pass in (s.stocks-> 'value' ? 'XXX') in where condition, I would want the search to be optimal?

"Say When I pass in (s.stocks-> 'value' ? 'XXX') in where condition" If that is what you want to do, then that is what your example should show. — jjanes
– jjanes, Commented Feb 26, 2020 at 16:44
The where clause on the outer joined table turns the outer join back into an inner join — user330315
– user330315, Commented Feb 26, 2020 at 19:08

jjanes · Accepted Answer · 2020-02-26 21:31:05Z

3

You can not index the results of a set returning function (other than making a materialized view).

We can reason out that if a.accounts @> '{"role": "ADVISOR"}' than it is necessary that t.accounts @> '[{"role": "ADVISOR"}]'. PostgreSQL can't reason that out, but we can.

However, this also won't help, because you are doing left joins. If every single row of t1 is getting returned, what do expect an index to accomplish?

With your added WHERE clause, you can use a JSONPATH (if you are using the latest version of PostgreSQL) to get the rows of t1 that you seem to want. It will use the index on t1 (stocks), either with or without the jsonb_path_ops:

WHERE (s.stocks -> 'value' ? 'XXX') AND
    t.stocks @? '$[*] ? (@.type == "RIC" ) ? (exists (@.value.XXX))';

However, the index is not actually very efficient if almost all entries have a type RIC, so this is pyrrhic victory.

edited Feb 26, 2020 at 21:31

answered Feb 26, 2020 at 16:42

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

You can also use WHERE s.stocks @> '{"value": "XXX"}' with older PostgreSQL versions.

jjanes Over a year ago

But based on its use with ?, I think "XXX" is a key, not a value. '{"value": {"XXX":something}}' You can't use containment on a key without specifying a value for it, that I can see. (You can do it in JSONPATH, but it doesn't seem to use an index effectively.)

Collectives™ on Stack Overflow

jsonb gin index not being used in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related