How to create gin index with gin_trgm_ops?

[ChandlerBent]

Hello.

I met some problem about property index.

I want to create gin index with gin_trgm_ops. Then I got a error message: ERROR: operator class "gin_trgm_ops" does not accept data type jsonb

create index: CREATE PROPERTY INDEX zk_trgm_3_index ON term_zk USING gin (name gin_trgm_ops)

How should I solve it?

Thank you very much!

[ChandlerBent]

if I create a gin index with no ops for property

Using match query does not hit the index.

I wonder that how to use the gin index?

cypher: explain match (n:term_zk) where n.name = 'Immunization' return n

Plan:

  Workers Planned: 2
  ->  Parallel Seq Scan on term_zk n  (cost=0.00..38506.10 rows=2621 width=32)
"        Filter: (properties.'name'::text = '""Immunization for feline leukemia""'::jsonb)"

DDL:

create table term_zk
(
    id         graphid default graphid(graph_labid('agens.term_zk'::cstring),
                                       nextval('agens.term_zk_id_seq'::regclass)) not null
        constraint term_zk_pkey
            primary key,
    properties jsonb   default jsonb_build_object()                               not null
)
    inherits (ag_vertex);

comment on table term_zk is 'base table for graph label agens.term_zk';

alter table term_zk
    owner to postgres;

create index zk_trgm_gin_index
    on term_zk using gin ((properties.'name'::text));

[joefagan]

Newbie alert - I hope it helps. This works but supports indexing of @> operator only. Is this indexing sufficient for you needs?

CREATE property INDEX idxginp ON term_zk USING GIN (name jsonb_path_ops);

[kysmou]

We hope that the article below will help you.

create index idx_student_gin1 on ks_graph.student using gin ((properties->>'name') gin_trgm_ops); analyze test_graph.student; explain select * from test_graph.student a where a.properties->>'name' like '%john%';

                                QUERY PLAN

---

Bitmap Heap Scan on student a (cost=4776.03..4791.85 rows=4 width=740) Recheck Cond: ((properties ->> 'name'::text) ~~ '%john%'::text) -> Bitmap Index Scan on idx_student_gin1 (cost=0.00..4776.03 rows=4 width=0) Index Cond: ((properties ->> 'name'::text) ~~ '%john%'::text) (4 rows)

[ChandlerBent]

We hope that the article below will help you.

create index idx_student_gin1 on ks_graph.student using gin ((properties->>'name') gin_trgm_ops); analyze test_graph.student; explain select * from test_graph.student a where a.properties->>'name' like '%john%';

                                QUERY PLAN

Bitmap Heap Scan on student a (cost=4776.03..4791.85 rows=4 width=740) Recheck Cond: ((properties ->> 'name'::text) ~~ '%john%'::text) -> Bitmap Index Scan on idx_student_gin1 (cost=0.00..4776.03 rows=4 width=0) Index Cond: ((properties ->> 'name'::text) ~~ '%john%'::text) (4 rows)

Hi @kysmou . Thank you for reply.

They way only work for normal pg query.

If I use cypher query will not hit this index.

[ChandlerBent]

Newbie alert - I hope it helps. This works but supports indexing of @> operator only. Is this indexing sufficient for you needs?

CREATE property INDEX idxginp ON term_zk USING GIN (name jsonb_path_ops);

Hi. It's sorry for late to reply you.

It seen my expected is different from gin_trgm_ops