In .NET 8 with Npgsql and Dapper, our query is incredibly slow compared to executing it directly through another database manager.

In our code, we execute queries as such:

var query = "select count(*) from mytable me where me.org_id = @orgId";

var result = await QueryAsync<int>(query, new { orgId, });
return result.Single();

The repository code that is called:

protected async Task<IEnumerable<T>> QueryAsync<T>(string query, object values)
{
    // These are fast
    await using var connection = await _postgresDataSource.OpenConnectionAsync(); // NpgsqlConnection
    await using var transaction = await connection.BeginTransactionAsync();
    await SetConfig(connection); // Runs "set role user;"

    // This is the slow part!
    return await connection.QueryAsync<T>(query, values, transaction, 
                                          commandType: CommandType.Text);
}

When running the exact same query in DBeaver / pgcli / psql, inside the transaction and with the same role, it executes in less than a second. In our application, it can take over 10 seconds.

Furthermore:

• Removing the BeginTransactionAsync makes it fast. However, we require a transaction to perform Row Level Security, hence setting the role.
• The Postgres database has plenty of resources available and has very low utilisation
• The connection from the application is pooled (Pooling=true in connection string)

The query itself is optimised and with indexes, so it seems to be an issue in the application only.

What could be the cause of this slowness? How can we further debug this?

More info:

Npgsql: 8.0.3
Dapper: 2.1.44
PostgreSQL: 15.7