Question 332053 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – indexing pattern on jsonb column

I want to improve performance of my queries using GIN index on jsonb column for pattern matching

For example, I have a table defined as:

CREATE TABLE my_table (
  uuid text,
  doc jsonb
);

In every row of table doc has path {A,B}. B is an object, possibly empty. There is a one more optional element in path: it can be absent, can be C, D etc.

Can I create a single index for pattern matching that will be used in all cases listed below?

SELECT doc#>>'{A,B}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%';

SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B,C}' ILIKE '%example%';

SELECT doc#>>'{A,B,D}'
FROM my_table
WHERE doc#>>'{A,B,D}' ILIKE '%example%';

I’ve tried to create index as:

CREATE INDEX my_index
ON my_table
USING GIN ((doc#>>'{A,B}') gin_trgm_ops)

Unfortunately, it works only for queries with path {A,B}, but not {A,B,C}

Tags:

2

Answers


  1. 0

    One index might work for all the cases, but you do have to write the query in an unnatural way for ones that don’t exactly match the index expression:

    SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%';

    doc#>>'{A,B,C}' ILIKE '%example%' usually implies that doc#>>'{A,B}' ILIKE '%example%', but that is not something PostgreSQL will reason out for you. It also isn’t perfectly true in all cases, characters that require escaping in JSON will be escaped in doc#>>'{A,B}' but not in doc#>>'{A,B,C}' if the end result of one is stringified JSON object while the other is just a plain string.

    select '{"0":{"a":"b"c"}}'::jsonb #>> '{0}';
---------------
 {"a": "b"c"}

select '{"0":{"a":"b"c"}}'::jsonb #>> '{0,a}';
----------
 b"c
    Login or Signup to reply.
  2. 0

    doc has path {A,B}. B is an object, possibly empty. There is one more optional element in path: it can be absent, can be C, D etc.

    This sounds like you could pull it out to a generated column, index that and simplify those queries to just one:

    ALTER TABLE my_table ADD COLUMN b_c_d text 
  GENERATED ALWAYS AS
  (trim(jsonb_path_query_first(doc,'$.A.B.**?(@.type()=="string")')::text,'"')) 
  STORED;

CREATE INDEX my_index
ON my_table
USING GIN (b_c_d gin_trgm_ops);

SELECT b_c_d
FROM my_table
WHERE b_c_d ILIKE '%example%';

    In this db<>fiddle demo it seems to perform slightly better, at the obvious cost of space.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search