How to improve performance of "minus" query on JSONB field in big table?

Question

In a PostgreSQL 12.7 database I have table with a JSONB field. Table has million rows. table1 is like:

id    | value
--    | --------------------------------------
1     | {"1001": "coke", "1002": "soda"}
2     | {"2001": "fanta", "1001": "coke"}
3     | {"3001": "beer", "2001": "fanta"}

Whenever, I need to remove items 1001, I have query like:

Query 1:

UPDATE table1 set value = value - '1001'

Query 2:

UPDATE table1 set value = value - '1001' WHERE value ? '1001'

Query 2 takes about 2 seconds and Query 1 takes around 20 seconds. I am looking way improve update speed somewhere quarter of seconds.

Answer 1

Support query 2 with an index. Like:

CREATE INDEX ON table1 USING gin (value);

The default GIN operator class for jsonb supports the ? operator.

While only few rows are affected, that should be very effective. If a major percentage of all rows is changed, indexes cannot help (would actually add cost).