Sql optimization

[vplemyannik]

I have a case when i have to find documents that contain one or more numbers.

var searchNumbers = new int[] { 1, 2, 3 ... };

        session.Query<Policy>()
            .Where(x => x.ItemNumbers.Any(number => searchNumbers .Contains(number)));

The generated sql looks like

WITH mt_temp_id_list1CTE as (
select id, unnest(CAST(ARRAY(SELECT jsonb_array_elements_text(CAST(d.data ->> 'ItemNumbers' as jsonb))) as integer[])) as data from service.mt_doc_policy as d
)
  , mt_temp_id_list2CTE as (
select id, data from mt_temp_id_list1CTE as d where data = ANY(ARRAY[1,2,3]) -- this array is  a parameter
)
 select data from service.mt_doc_policy as d where id in (select id from mt_temp_id_list2CTE)

It works quite long , i have 12000 documents and this query takes 6s.
Is it possible to convert such Where Expression into

SELECT * FROM service.mt_doc_policy WHERE (data->'ItemNumbers') @> ANY(ARRAY['[370367]', '[371361]']::jsonb[])

or something like that?
At least GIN index will works in this case , with index this query takes 60ms and 500ms without

Also i have noticed that CompiledQuery does not work when parameter is array/list/ienumerable and etc.
I am getting error Members IEnumerable<int> ItemNumbers cannot be used as parameters to a compiled query

 public class FindPoliciesByItemNumbers : ICompiledQuery<Policy, IEnumerable<ShortPolicy>>
    {
        public IEnumerable<int> ItemNumbers { get; init; }

        public Expression<Func<IMartenQueryable<Policy>, IEnumerable<ShortPolicy>>> QueryIs()
        {
            return query
                => query.Where(x => x.ItemNumbers.Any(item => ItemNumbers.Contains(item)))
                    .Select(
                        x => new ShortPolicy()
                        {
                            ItemNumbers = x.ItemNumbers,
                            Id = x.Id,
                            // .... other fields
                        });
        }
    }

As i understand ItemNumbers it is a just parameter for the sql query, could we just compile query once and use different arrays as a parameter?
P.S. Thank you for this project, i am very exited about using it

[jeremydmiller]

@davilko Supporting Linq is absurdly complicated. So yeah, we could make the Linq parsing smart enough to understand when it could use the optimized SQL you wrote up there instead of falling back to the CTE expressions that are necessary for SelectMany() and other random things. That's nothing elegant, just brute force if/then logic to get it done.

In the meantime, you can happily bypass Linq and use the Query<DocumentType>(where clause) syntax to get around it.

"As i understand ItemNumbers it is a just parameter for the sql query, could we just compile query once and use different arrays as a parameter?" -- it might not be that hard to add IEnumerable support as parameters to the compiled query support. Wasn't honestly anything I thought about while doing this. Essentially, the compiled query support figures out how the compiled query class members relate to an NpgsqlParameter by tracking a unique value to the query type as its doing the planning. We just don't have that for IEnumerable<T> or T[] at the moment, but that might be doable in the future.