How to make a multi column JSON index in Postgres?

Question

I have a query where I query by a normal text column and a value in a JSON column. What I'm wondering is how to create the best index for the query?

This is the query:

explain select * from "tags" where "slug"->>'en' = 'slugName' 
and "type" in ('someType1','someType1');
-------
Seq Scan on tags  (cost=0.00..1.47 rows=1 width=888)
"  Filter: (((type)::text = ANY ('{dsfdsf,fgsdf}'::text[])) AND ((slug ->> 'en'::text) = 'dsfdsf'::text))"

The "slug" column is type JSON and the "type" column is type varchar(191). I'm familiar that I can add an index to the JSON column like:

CREATE INDEX tag_slug_index ON tags USING btree ((slug ->> 'en'));

But I'm wondering, how do I create a multi-column index on the slug name combined with the type column?

Answer 1

There is nothing special about it, you just do it the normal way, by separating them with a comma:

CREATE INDEX ON tags USING btree ("type", (slug ->> 'en'));

The expression does still need to be in an extra set of parentheses, same is if it were the only 'column' in the index.