How to Index and make WHERE clause case insensitive?

user_5063031 0
Viewed 48

Have this table in PostgreSQL 12, no index

CREATE TABLE tbl
(
    ...
    foods json NOT NULL
)

sample record:

foods:
{ 
    "fruits": [" 2 orange ", "1 apple in chocolate", " one pint of berry"],
    "meat": ["some beef", "ground beef", "chicken",...],
    "veg": ["cucumber"]
}

Need to select all records who satisfy:

  1. fruits contains orange.
  2. AND meat contains beef or chicken.

select * from tbl where foods->> 'fruits' LIKE '%ORANGE%' and (foods->> 'meat' LIKE '%beef%' or foods->> 'meat' LIKE '%chicken%')

Is it an optimized query? (I'm from RDBMS world)

How to index for faster response and not overkill, also how to make PostgreSQL case insensitive?

postgresql
1 Answers

This will make you unhappy.

You would need two trigram GIN indexes to speed this up:

CREATE EXTENSION pg_trgm;

CREATE INDEX ON tbl USING gin ((foods ->> 'fruits') gin_trgm_ops);
CREATE INDEX ON tbl USING gin ((foods ->> 'meat') gin_trgm_ops);

These indexes can become large and will impact data modification performance.

Then you need to rewrite your query to use ILIKE.

Finally, the query might be slower than you want, because it will use three index scans and a (potentially expensive) bitmap heap scan.

But with a data structure like that and substring matches, you cannot do better.

user_64643080
Related