How to make a multi column JSON index in Postgres?-CodePudding

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?

CodePudding user response：

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.