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?

Can you create a `generated` column for the json element , and use that column and type col in a index? — Anand Sowmithiran, Jun 12 '22 at 16:42
Ah that's a good idea. I haven't worked with generated columns before tho. How would that work / what would i do? — Connor Leech, Jun 12 '22 at 16:48
See this [post](https://stackoverflow.com/a/8250729/14973743) for computed columns — Anand Sowmithiran, Jun 12 '22 at 17:04
postgres allows indexes on json see https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/ — nbk, Jun 12 '22 at 17:22

score 0 · Accepted Answer · answered Jun 12 '22 at 18:04

0

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.

answered Jun 12 '22 at 18:04

jjanes

37,812
5
27
34

Okay this does work but the query is still using `Seq Scan` instead of the index. The index condition is `(type, ((slug ->> 'en'::text)))` and the Seq Scan filter is: `(((type)::text = 'someType'::text) AND ((slug ->> 'en'::text) = 'slugName'::text))`. Is there something I need to update or is the seq scan just a better query plan than using an index in this case? – Connor Leech Jun 12 '22 at 18:19
1

Based on the plan estimates, your table is so small it would make no sense to use an index. – jjanes Jun 12 '22 at 19:31

How to make a multi column JSON index in Postgres?

1 Answers1