npgsql: Migration from 2.2.7 to 6.0 made the execution of commands slower

Migration from 2.2.7 to 6.0 made the execution of commands slower.

DB

PostgreSQL version: 13.3

target table

How I do it in 2.2.7

Connection string: "UserId=postgres;Password=pass;Server=db;Database=database;Timeout=30;CommandTimeout=240;Port=5432;Encoding=UNICODE;Pooling=true;MaxPoolSize=100;"

1. Open connection:

public static NpgsqlConnection OpenConnection(string connectionString = null) {
  NpgsqlConnection connection = null;
  try {
    if (connectionString.IsNullOrEmpty())
      connection = new NpgsqlConnection(GetConnectionString());
    else
      connection = new NpgsqlConnection(connectionString);
    connection.Open();
  } catch (Exception ex) {
    EventLogHelper.WriteEntry(ex);
  }
  if (connection != null && connection.State != ConnectionState.Open)
    connection = null;
  return connection;
}

1. Command & Execution

public static CacheResult TryFindCache(string base64Hash, DBConn dbConn) {
  try {
    using var command =
        new NpgsqlCommand("SELECT output FROM calccache WHERE hash = @hash",
                          dbConn.Connection, dbConn.Transaction);
    command.Parameters.AddWithValue("@hash", base64Hash);
    using(NpgsqlDataReader reader = command.ExecuteReader()) {
      if (reader.HasRows && reader.Read()) {
        string output = reader.GetString(0);
        CacheResult result = ...
        return result;
      }
    }
  } catch (Exception ex) {
    ...
  }
  return null;
}

1. dotTrace - 19 ms.

How I execute my command in 6.0:

Connection string: "Host=db;Port=5432;Username=postgres;Password=pass;Database=database;Timeout=30;Command Timeout=240;Pooling=true;Minimum Pool Size=10;Maximum Pool Size=100;Persist Security Info=true"

1. Open connection - same
2. Command & Execution

public static CacheResult TryFindCache(string base64Hash, DBConn dbConn) {
  try {
    using var command =
        new NpgsqlCommand("SELECT output FROM calccache WHERE hash = @hash",
                          dbConn.Connection, dbConn.Transaction);
    command.Parameters.AddSmart("@hash", base64Hash);
    using(NpgsqlDataReader reader = command.ExecuteReader()) {
      if (reader.Read()) {
        string output = reader.GetString(0);
        CacheResult result = ...
        return result;
      }
      reader.Close();
    }
  } catch (Exception ex) {
    ...
  }
  return null;
}

1. Custom extension for parameters:

public static NpgsqlParameter AddSmart<T>(
    this NpgsqlParameterCollection collection, string paramName, T value,
    NpgsqlDbType? type = null, bool keepEmptyString = true) {
  // Is nullable?
  NpgsqlParameter parameter;
  if (value is null) {
    parameter = new NpgsqlParameter(paramName, DBNull.Value);
  } else {
    if (value is string strValue) {
      var isNull = keepEmptyString && strValue == string.Empty;
      parameter = new NpgsqlParameter(paramName, isNull ? DBNull.Value
                                      : strValue);
    } else {
      parameter = Nullable.GetUnderlyingType(typeof(T)) !=
                  null
                        ? new NpgsqlParameter(paramName, value)
          : new NpgsqlParameter<T>(paramName, value);
    }
  }

  if (type.HasValue) parameter.NpgsqlDbType = type.Value;

  collection.Add(parameter);
  return parameter;
}

1. dotTrace - 107 ms.

The issue

As we can see in the previous screenshots, the difference in execution of ExecuteReader() is huge. Any thoughts on why it’s so slow? And is there any possible solution?

Operating system: Windows