Is it possible to create a PostgreSQL index with json Include Columns?

Question

I am using PostgreSQL 11.8

CREATE INDEX ix_products_product_sku_with_json_columns
ON products USING btree((product_sku))
INCLUDE ((data->>'variantCode'));

ERROR: expressions are not supported in included columns
SQL state: 0A000

Documentation says:

Expressions are not supported as included columns since they cannot be used in index-only scans.

I suppose data->>'variantCode' is an expression and that's the end of the story?
I can see two workarounds, none of which looks appealing to me:

create a dedicated VariantCode regular SQL column in the table
put data->>'variantCode' as part of the index keys

How bad is the second option?
Ultimately the goal would be to do an Index-Only Scan in the following query:

SELECT data->>'variantCode', ARRAY_AGG(product_sku)
FROM products
GROUP BY data->>'variantCode'

Laurenz Albe · Accepted Answer · 2021-08-27 17:11:06Z

7

You cannot have data->>'variantCode' as INCLUDE column, but also adding it as index key won't help you to get an index-only scan. PostgreSQL doesn't consider index keys that are not columns for an index-only scan. There is no fundamental problem that prevents that, it is just not implemented.

You'd have to add the whole data to the index, but that is not possible, since there is no B-tree operator class for jsonb.

So the only remaining option to get an index-only scan would be a generated column:

ALTER TABLE products ADD data_varcode text
   GENERATED ALWAYS AS (data->>'variantCode') STORED;

Then you can use that column in your query and index.

answered Aug 27, 2021 at 17:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Romain Hautefeuille Over a year ago

Indeed, I couldn't achieve Index-Only Scans using that json column in the index keys, only Bitmap Heap Scan. Unfortunately I am running PostgreSQL 11.8 which doesn't include Generated Columns, but I can still create a regular column. Thanks

Collectives™ on Stack Overflow

Is it possible to create a PostgreSQL index with json Include Columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related