I have a query involving a JSONB column in PostgreSQL, structured as follows:
select ((option ->> 'fruit_id'))
from table1 t1
where ((option ->> 'fruit_id')) = '1'
limit 10
This query is designed to fetch data from a JSONB column named option, extracting the value associated with the key 'fruit_id'.
I have created a B-tree index on the expression (option ->> 'fruit_id') to optimize this query. According to the PostgreSQL documentation, this setup seems to be a suitable candidate for an index-only scan.
However, the query appears to be using the index(index scan) but not performing an index-only scan. What could be the reason for this behavior? Is there something specific about JSONB indexing in PostgreSQL that prevents an index-only scan in this scenario?
I applied the vacuum command to the table, but even after that, index-only-scan was not performed.
The index type must support index-only scans. B-tree indexes always doFrom the same docs you referenced:
In principle, index-only scans can be used with expression indexes. For example, given an index on f(x) where x is a table column, it should be possible to execute SELECT f(x) FROM tab WHERE f(x) < 1; as an index-only scan; and this is very attractive if f() is an expensive-to-compute function. However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index. In this example, x is not needed except in the context f(x), but the planner does not notice that and concludes that an index-only scan is not possible.
You are using an operator, not a function, but that makes no difference to the planner, it is still an expression.