marten
marten copied to clipboard
Query on child collection can't use GIN Index
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.