High Latency in Data Retrieval Using Entity Framework Core with PostgreSQL
I'm a bit lost with I'm attempting to set up I'm wondering if anyone has experience with I'm working through a tutorial and I'm experiencing high latency in data retrieval when using Entity Framework Core 6 with a PostgreSQL database... My query to fetch user data is taking over 500 ms, which is unacceptable for our application's performance requirements. The query is relatively simple, but I'm not sure if I'm missing some optimization tricks. Here's the code I'm using: ```csharp public async Task<List<User>> GetUsersAsync(int pageNumber, int pageSize) { return await _context.Users .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToListAsync(); } ``` I've made sure to have proper indexes set up on the `Users` table, particularly on the columns involved in filtering and sorting. However, when I run the same query directly in pgAdmin, it executes in around 20 ms, which makes me suspect there's something wrong with the way EF Core is translating the query. Additionally, I've enabled logging for EF Core to see the generated SQL and noticed that it includes unnecessary joins in some cases where they shouldn't be needed. I've tried explicitly selecting only the fields I need to reduce the data being retrieved, like this: ```csharp public async Task<List<UserDto>> GetUserDtosAsync(int pageNumber, int pageSize) { return await _context.Users .Select(u => new UserDto { Id = u.Id, Name = u.Name }) .Skip((pageNumber - 1) * pageSize) .Take(pageSize) .ToListAsync(); } ``` Still, the performance hasn't improved significantly. I'm starting to wonder if there are any configuration settings or best practices I might be overlooking in Entity Framework Core, or if there's a better way to handle pagination with larger datasets. Any insights would be greatly appreciated! I'm on Ubuntu 20.04 using the latest version of C#. Is this even possible? Thanks for your help in advance! For context: I'm using C# on Windows 11. Any examples would be super helpful. The project is a service built with C#. Any examples would be super helpful. Any feedback is welcome!