marten icon indicating copy to clipboard operation
marten copied to clipboard

Query on child collection can't use GIN Index

Open Leh2 opened this issue 6 months ago • 0 comments

When creating an index on a child collection using MartenDB, there's a discrepancy in the syntax used for the index definition and the generated query. This discrepancy causes the index not to be utilized during query execution.

Index Definition:

options.Schema.For<SomeModel>()
    .Index(x => x.ChildCollection, index => index.ToGinWithJsonbPathOps());

Created Index:

CREATE INDEX IF NOT EXISTS mt_doc_somemodel_idx_childcollection
    ON public.mt_doc_somemodel USING gin
    ("(data -> 'ChildCollection'::text)" jsonb_path_ops)
    TABLESPACE pg_default;

Generated Query:

SELECT d.id, d.data 
FROM public.mt_doc_somemodel AS d
WHERE CAST(d.data ->> 'ChildCollection' AS jsonb) @> '[{"SomeId":1}]';

Problem: The index is created using the -> operator: "(data -> 'ChildCollection'::text)" jsonb_path_ops. However, the generated query uses the ->> operator: CAST(d.data ->> 'ChildCollection' AS jsonb).

This mismatch in syntax (i.e., -> vs. ->>) prevents the index from being used, leading to potential performance issues as the query cannot take advantage of the GIN index.

Leh2 avatar Aug 22 '24 08:08 Leh2