Understanding Index-Only Scan Behavior with JSONB Columns in PostgreSQL 13

Question

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.

Referenced document content

The index type must support index-only scans. B-tree indexes always do

jjanes · Accepted Answer · 2024-01-20 14:46:37Z

1

From 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.

answered Jan 20, 2024 at 14:46

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

2 Comments

Person239183218930 Over a year ago

If you check the execution plan of the query, you can see that it is running on the index I created. It's just not index-only-scan. So, why is it possible to use columns containing operators in index-scan, but not in index-only-scan?

jjanes Over a year ago

Because no one has written the code to make that happen.

Collectives™ on Stack Overflow

Understanding Index-Only Scan Behavior with JSONB Columns in PostgreSQL 13

Referenced document content

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Referenced document content

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related