ANY operator has significant performance problem when using an array as a parameter

Question

I started using 'ANY()' function in query instead of 'IN' due to some parameter bound error. Currently it's something like that.

Select * 
FROM geo_closure_leaf 
WHERE geoId = ANY(:geoIds)

But it has a huge impact on performance. Using the query with IN is very much faster than with ANY.

Any suggestion how can we bound array of string parameters can be passed in 'IN' expression.

I have tried temporary fix using

Select * 
FROM geo_closure_leaf 
WHERE geoId IN (''('' || array_to_string(:geoIds::text[] ,''),('') || '')'')

Select * 
FROM geo_closure_leaf 
WHERE geoId IN (select unnest(:geoIds::text[]))

geoIds = array of strings

It's working this way.

**public override T Query<T>(string query, IDictionary<string, object> parameters, Func<IDataReader, T> mapper)**
        {
            T Do(NpgsqlCommand command)
            {
                IDataReader reader = null;
                try
                {
                   ** command.CommandText = query;
                    reader = command.AddParameters(parameters).ExecuteReader();**
                    return mapper(reader);
                }
                finally
                {
                    CloseDataReader(reader);
                }
            }

            return Execute(Do);
        }

Object is array of string.

Expected is: I should be able to do this without having to put extra logic in sql.

Select * 
FROM geo_closure_leaf 
WHERE geoId IN (:geoIds)

Answer 1

https://dba.stackexchange.com/questions/125413/index-not-used-with-any-but-used-with-in

Found this post explaining how indeexs are getting used in different constructors of 'ANY' & 'IN'.