Indexing Entire JSON Column vs Indexing Specific JSON Field in PostgreSQL

Question

I have a PostgreSQL table named item_log with a log column of type jsonb that contains various attributes. One of these attributes is delete_log. I'm querying this table based on the created_at field and the presence of non-null values in the delete_log attribute.

SELECT item_id
FROM item_log
WHERE created_at >= CURRENT_DATE - INTERVAL '15 days'
  AND (log->>'delete_log') IS NOT NULL;

I'm considering different indexing strategies for optimizing my queries:

Here is what I have tried,

Created index on created_at: Query became faster when I didn't put the condition (log->>'delete_log') IS NOT NULL in the query which is expected. As soon as I put this condition or even select this field it took around 40 minutes.

I guess that the query slowdown occurs due to the inclusion of delete_log. When this field is queried, it starts reading the TOAST table and performs deTOAST operations, which increases I/O time significantly.

Now to optimize the query I have a couple of options:

• Add composite index on (created_at, log->>delete_log)
• Add index on (log->>delete_log) as I already have index on created_at?

Also, I have another confusion.

• What index type should I choose for log->>delete_log?
• And, How is it different from indexing the whole log field itself in terms of performance or storage for the above query?

Answer 1

Indexing the whole JSON with a GIN index would not help a lot with your query.

The best index is a partial index that indexes only the rows that have a non-NULL delete_log:

CREATE INDEX ON item_log (created_at) WHERE log->>'delete_log' IS NOT NULL;

That index is small, and it doesn't have to be modifies for rows that do not satisfy the WHERE condition. The disadvantage is that this index might only be useful for this single query, and you want as few indexes as possible on a table that receives many INSERTs. So if you have other queries on that table, the overall better solution could be to use a less specific index that is not the perfect choice for the query at hand, but can support your other queries as well.

Answer 2

The GIN index on jsonb is large, and cannot be used for idxcol->> 'a' IS NOT NULL queries. You might want it for other reasons, but it won't do anything for this query.

The composite btree index on (created_at, (log->>delete_log)) is a good one. It will avoid the need to deTOAST the data (provided the select list is exactly what you show). It won't be particularly efficient inside the index, because neither of the columns are tested for simple equality, so it will need to scan the entire part of the index meeting the inequality condition, individually removing rows failing the IS NOT NULL. But if avoiding the deTOAST is all you need to do, it will be good enough.

Separate indexes on (created_at) and ((log->>delete_log)) could work, being used by combining with a BitmapAnd operation. But the planner is likely to overlook doing that. The problem is that the planner doesn't take deTOAST cost into account, so if the only point of including the extra index is to avoid the deTOAST, it might not bother with it. Similarly, if you have both indexes (created_at, (log->>delete_log)) and (created_at) it might not see the value in using the larger of the two, so it is best to avoid having that single column index as it is an attractive nuisance.