Vary Contains translation (@> vs. ANY) based on modeled indexes

[Dreamescaper]

Moved from dotnet/efcore#36215.

What problem are you trying to solve?

We have a table with filter conditions. Currently Ids are stored as arrays.

public class Conditions
{
    public string[] Property1Values { get; set; }
    public string[] Property2Values { get; set; }
}

Then we get values from other table based on those conditions:

var count = await dbContext.Conditions
    .Select(c => dbContext.Entities.Where(e => c.Property1Values.Contains(e.Property1) &&  c.Property2Values.Contains(e.Property2)).Count())
    .ToListAsync();

EF Core translates this query into something like this:

SELECT  (
    SELECT count(*)::int
    FROM "Entities" AS a0
    WHERE a."Property1Values" @> ARRAY[a0."Property1"]::text[] AND a."Property2Values" @> ARRAY[a0."Property2"]::text[])
FROM "Conditions" AS a

I.e. it creates an array from a single value, and compares them via @> operator. In this case Postgres ignores indexes, and on a huge table (60m rows) it executes more than 1 minute.

On the other hand, if I rewrite this query to this:

SELECT  (
    SELECT count(*)::int
    FROM "Entities" AS a0
    WHERE a0."Property1" = ANY(a."Property1Values") AND a0."Property2" = ANY(a."Property2Values"))
FROM "Conditions" AS a

It uses indexes for Property1 and Property2, and executes in ~1ms.

Describe the solution you'd like

EF Core should translate Array.Contains as ANY operator instead of creating single-element arrays.

[Dreamescaper]

@roji

@Dreamescaper what kind of index do you have on your array columns? The @> operator is supposed to work well with GIN operators.

Regardless, I'm closing this issue as specific to the PostgreSQL provider (this is the general EF repo). Please don't answer here, but rather reopen (if necessary) on npgsql/efcore.pg.

I have no indexes on array columns. I do have regular B-TREE indexes on the other side - Entity.Property1 and Entity.Property2.

[Dreamescaper]

I can see some related issue, requesting the opposite (i.e. current behavior - should it be closed as done?)
#460

[roji]

I can see some related issue, requesting the opposite (i.e. current behavior - should it be closed as done?)
#460

Thanks, yeah - I just closed that one as requesting the current behavior.

I have no indexes on array columns. I do have regular B-TREE indexes on the other side - Entity.Property1 and Entity.Property2.

OK, I understand now - thanks for the extra context.

I think what we need is to have the provider generate different SQLs based on the indexes defined in the EF model. That is, if there's a (GIN) index on the column, translate to @>, if there's an index on the other side translate to ANY. The current strategy (code) is to pick the translation based on what the array is, without looking at actual indexes: if it's a column we assume a GIN index and translate to @>, in most other cases we translate to ANY.

Otherwise, at some point we could consider exposing explicit mechanisms to allow users to pick the specific translation mechanism via EF.Functions. However, leaving the decision-making inside the provider - based on modeled indexes - seems to make sense.

[Dreamescaper]

I'm attaching a small console repro project (with Testcontainers). It creates context, feeds data, invokes both queries and prints EXPLAIN ANALYZE.

EfCorePgRepro.zip

[roji]

Doing this properly depends on dotnet/efcore#36231 (I've submitted a PR). Partial work in https://github.com/roji/efcore.pg/tree/ContainsIndexes.