GraphQL Ruby N+1 Batching Issue

Question

I am using GraphQL in Ruby with the Batch & QueryResolver gems. They work fantastic except for this issue and can't seem to figure out why this may be happening. It seems to be loading "all relationships" then loading "first: X" relationships shortly after that. That's causing hundreds of IDs to be passed into a SELECT * ... IN (...) query vs just doing hte first: X in the IN () query. Here is my query:

query getCenterPayments(
  $centerId: ID!
  $first: Int
) {
  viewer {
    id
    centers(id: $centerId) {
      edges {
        node {
          id
          currency
          payments(
            first: $first
          ) {
            pageInfo {
              hasNextPage
              hasPreviousPage
              endCursor
              startCursor
            }
            edges {
              cursor
              node {
                id
                participant { <---- N+1 Queries here
                  id
                  name
                }
              }
            }
          }
        }
      }
    }
  }
}

Here is the queries being executed in the background...

User Load (0.4ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2[["id", 1], ["LIMIT", 1]]
  Center Load (1.4ms)  SELECT "centers".* FROM "centers" INNER JOIN "members" ON "centers"."id" = "members"."center_id" WHERE "members"."user_id" = $1 AND (centers.uid = 'xxxxx' OR centers.slug = 'xxxxx')  [["user_id", 1]]
  Payment Load (15.5ms)  SELECT "payments".* FROM "payments" WHERE "payments"."center_id" = 1
  User Load (7.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (2, 3, 4, 5,6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49,50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92,93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145,146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231,232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317,318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403,404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489,490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 502, 503, 504, 505, 506, 507)
  Member Exists (0.7ms)  SELECT  1 AS one FROM "members" WHERE "members"."center_id" = $1 AND "members"."discarded_at" IS NULL AND "members"."role" = $2 AND "members"."user_id" = $3 LIMIT $4  [["center_id", 1], ["role", "admin"], ["user_id", 1], ["LIMIT", 1]]
  Payment Load (13.9ms)  SELECT "payments".* FROM "payments" WHERE "payments"."center_id" = $1 ORDER BY "payments"."date" DESC  [["center_id", 1]]
  User Load (7.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (237, 149, 298, 506, 278, 497, 76, 138, 139, 4, 61, 274, 449, 450, 243, 311, 93, 239, 496, 307, 78, 59, 453, 499, 500, 490, 489, 447, 337, 437, 300, 255, 187, 186, 446, 387, 401, 371, 481, 502, 390, 340, 341, 504, 505, 494, 151, 7, 465, 393, 174, 419, 103, 487, 503, 454, 498, 417, 418, 495, 472, 58, 442, 501, 263, 275, 285, 476, 486, 140, 116, 479, 493, 488, 178, 507, 226, 229, 372, 373, 134, 133, 228, 483, 484, 114, 431, 258, 463, 468, 462, 441, 68, 459, 460, 438, 403, 404, 148, 242, 204, 439, 46, 70, 127, 429, 492, 350, 317, 366, 491, 358, 360, 150, 84, 407, 455, 456, 164, 363, 365, 413, 155, 402, 485, 238, 482, 314, 315, 142, 221, 480, 478, 383, 389, 470, 85, 458, 477, 445, 467, 466, 250,251, 412, 411, 165, 475, 432, 461, 474, 216, 361, 334, 473, 436, 451, 427, 471, 464, 272, 271, 422, 302, 95, 94, 469, 400, 380, 231, 434, 448, 378, 457, 67, 382, 381, 430,6, 452, 206, 428, 220, 367, 368, 444, 167, 408, 443, 130, 426, 109, 440, 224, 410, 24, 421, 435, 312, 313, 433, 384, 335, 283, 423, 424, 357, 392, 141, 425, 388, 30, 420, 168, 266, 321, 336, 416, 415, 414, 377, 399, 406, 83, 288, 292, 409, 405, 398, 396, 351, 359, 297, 397, 395, 394, 391, 214, 105, 364, 369, 386, 370, 333, 379, 296, 40, 385,352, 189, 346, 342, 375, 376, 332, 353, 374, 338, 323, 356, 354, 236, 362, 355, 347, 185, 326, 325, 324, 179, 348, 349, 219, 166, 249, 193, 345, 322, 343, 344, 111, 316, 299, 339, 331, 203, 276, 31, 293, 304, 329, 112, 113, 328, 327, 330, 319, 241, 240, 91,308, 309, 270, 310, 320, 55, 318, 210, 306, 200, 102, 247, 248, 235, 173, 305, 232, 264, 201, 202, 154, 217, 294, 265, 252, 303, 12, 213, 147, 301, 282, 286, 287, 291, 295, 290, 279, 289, 284, 38, 225, 227, 106, 245, 280, 19, 281, 244, 277, 159, 273, 268, 77, 269, 51, 246, 234, 230, 65, 66, 260, 56, 267, 262, 223, 261, 146, 259, 254, 253, 190, 257, 256, 181, 74, 162, 97, 175, 121, 233, 171, 194, 16, 188, 163, 222, 60, 41, 212, 208, 215, 218, 137, 26, 207, 209, 176, 211, 195, 131, 132, 144, 136, 196, 197, 199, 143, 129, 180, 184, 71, 156, 183, 128, 34, 191, 192, 205, 11, 198, 182, 152, 82, 29, 172, 27, 28, 170, 169, 177, 101, 99, 145, 17, 160, 48, 49, 124, 100, 161, 108, 107, 122, 157, 158, 123, 115, 110, 125, 8, 153, 57, 135, 44, 9, 119, 126, 3, 10, 13, 22, 75, 104, 118, 21, 120, 2, 117, 15, 50, 25, 90, 80, 96, 36, 47, 89, 87, 98, 23, 86, 92, 81, 88, 53, 63, 64, 37, 14, 73, 72, 18, 20, 32, 45, 39, 79, 42, 43, 62, 54, 33, 69, 52, 35, 5)
  Payment Load (0.6ms)  SELECT  "payments".* FROM "payments" WHERE "payments"."center_id" = $1 ORDER BY "payments"."date" DESC LIMIT $2  [["center_id", 1], ["LIMIT", 10]]
   (2.0ms)  SELECT COUNT(*) FROM "payments" WHERE "payments"."center_id" = $1  [["center_id", 1]]
  Center Load (0.5ms)  SELECT "centers".* FROM "centers" WHERE "centers"."id" = 1
  User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (237, 149, 298, 506, 278, 497, 76, 138, 139, 4)
Completed 200 OK in 680ms (Views: 1.6ms | ActiveRecord: 50.3ms)

As you can see it's loading a lot of unnecessary users although I am doing this for the resolver of the user:

field :participant do
  type UserType

  resolve -> (obj, args, ctx) {
    RecordLoader.for(User).load(obj.participant_id)
  }
end

Have you encountered this before as well?

Use `bullet` gem https://github.com/flyerhzm/bullet to find out the (N + 1) queries. — Salil, Apr 27 '18 at 16:41
Maybe you need to do `includes` on the associated records? I haven't used GraphQL with Rails yet (jealous!), but maybe Rails and GraphQL aren't getting along well. Bullet might be useful here to help diagnose as well. — Petercopter, Apr 27 '18 at 21:54
@emptywalls yeah that would work if I were doing a REST type of API but the GQL setup would produce overkill especially if I didn't want those fields. Lots of excess data. — Jonathan Reyes, Apr 29 '18 at 16:53
@emptywalls Yeah rails is pretty awesome for gql. Thus far it's probably my choice for small-medium projects. :D — Jonathan Reyes, Apr 29 '18 at 16:53

score 2 · Answer 1 · answered Apr 28 '18 at 09:28

The issue was in using both resolvers at the same time. Looks like the QueryResolver was resolving before limits as the next query resolver was looking to resolve its queries. It was causing mayhem. I ended up just going with Shopify's Batch gem and have been very pleased with it.

score 1 · Answer 2 · answered Jun 05 '18 at 07:15

1

You can use graphql-preload gem to preload associations in Graphql

https://github.com/ConsultingMD/graphql-preload

field :payment do
    preload :participants
    resolve -> (obj, args, ctx) {
      obj.participants
    }
end

answered Jun 05 '18 at 07:15

Krupa Suthar

670
3
14

GraphQL Ruby N+1 Batching Issue

2 Answers2