GraphQL generated resolvers are ignoring GSI in 1-to-many relationship list calls, doing table SCAN instead of QUERY

[jpangburn]

Environment information

System:
  OS: macOS 14.5
  CPU: (10) arm64 Apple M1 Pro
  Memory: 152.22 MB / 32.00 GB
  Shell: /bin/zsh
Binaries:
  Node: 22.2.0 - ~/.nvm/versions/node/v22.2.0/bin/node
  Yarn: undefined - undefined
  npm: 10.7.0 - ~/.nvm/versions/node/v22.2.0/bin/npm
  pnpm: undefined - undefined
NPM Packages:
  @aws-amplify/backend: 1.0.2
  @aws-amplify/backend-cli: 1.0.3
  aws-amplify: 6.3.4
  aws-cdk: 2.143.0
  aws-cdk-lib: 2.143.0
  typescript: 5.4.5
AWS environment variables:
  AWS_PROFILE = gatecode-deploy
  AWS_STS_REGIONAL_ENDPOINTS = regional
  AWS_NODEJS_CONNECTION_REUSE_ENABLED = 1
  AWS_SDK_LOAD_CONFIG = 1
No CDK environment variables

Description

The bug:

I have a model defined in resource.ts:

GateCodes: a
  .model({
    gatePhone: a.string().required(),
    ownerCode: a.string().required(),
    name: a.string().required(),
    // create a reference field to the account (there must be an account to have a GateCodes entry)
    owner: a.string().required(),
    // use that reference field to make a belongsTo relationship
    account: a.belongsTo('Account', 'owner'),
  }).identifier(['gatePhone', 'ownerCode'])
  .authorization(allow => [allow.owner()]),
Account: a
  .model({
    owner: a.string().required().authorization(allow => [allow.owner().to(['read'])]),
    comment: a.string(),
    gateCodes: a.hasMany('GateCodes', 'owner'),
  }).identifier(['owner'])
  .authorization(allow => [allow.ownerDefinedIn('owner')]),

So basically a single owner has one Account, and their account can have many GateCodes. The GateCodes schema uses the owner field as the reference field, so there's a Global Secondary Index on the owner field. However, when I fetch the GateCodes after loading an account like this:

this.account = data;
let result = await data?.gateCodes();
console.log("got gate codes- ", result);

You'd think it would do a query on the underlying Dynamo DB table because there's a GSI on the owner which is what it's using to query with. But it does not, it does a scan instead. In the CloudWatch logs (after turning on API logging in the AppSync settings) I see the following transformed template:

" \n{"version":"2018-05-29","limit":100,"filter":{"expression":"(((#owner = :and_0_or_0_owner_eq) OR (#owner = :and_0_or_1_owner_eq) OR (#owner = :and_0_or_2_owner_eq)) AND (#owner = :and_1_and_0_owner_eq))","expressionNames":{"#owner":"owner"},"expressionValues":{":and_0_or_1_owner_eq":{"S":"180173d0-4001-70c6-e87f-0e070c83caef"},":and_1_and_0_owner_eq":{"S":"180173d0-4001-70c6-e87f-0e070c83caef"},":and_0_or_0_owner_eq":{"S":"180173d0-4001-70c6-e87f-0e070c83caef::180173d0-4001-70c6-e87f-0e070c83caef"},":and_0_or_2_owner_eq":{"S":"180173d0-4001-70c6-e87f-0e070c83caef"}}},"operation":"Scan"}\n"

The very last entry in there you can see is \"operation\":\"Scan\". It's the same problem if you try to query the GateCodes directly by passing the owner in (which ought to trigger a query on the GSI as well):

this.client.models.GateCodes.observeQuery({
  filter: {
    owner: {
      eq: this.userService.currentOwner!
    }
  }
}).subscribe({
  next: ({ items, isSynced }) => {
    this.gateCodes = items;
  },
});

This also produces an \"operation\":\"Scan\" in the logs.

The culprit

In the AppSync functions it creates a resolver for this model called QueryListGateCodesDataResolverFn, which has the following relevant code:

#if( !$util.isNull($ctx.stash.modelQueryExpression) && !$util.isNullOrEmpty($ctx.stash.modelQueryExpression.expression) )
  $util.qr($ListRequest.put("operation", "Query"))
  $util.qr($ListRequest.put("query", $ctx.stash.modelQueryExpression))
  #if( !$util.isNull($args.sortDirection) && $args.sortDirection == "DESC" )
    #set( $ListRequest.scanIndexForward = false )
  #else
    #set( $ListRequest.scanIndexForward = true )
  #end
#else
  $util.qr($ListRequest.put("operation", "Scan"))
#end

This code says if there's no modelQueryExpression to do a scan. There's a related function called QuerylistGateCodespreAuth0Function which builds this variable. It's too long to put here, but it starts like this:

#set( $modelQueryExpression = {} )
## [Start] Validate key arguments. **
#if( !$util.isNull($ctx.args.ownerCode) && $util.isNull($ctx.args.gatePhone) )
  $util.error("When providing argument 'ownerCode' you must also provide arguments gatePhone", "InvalidArgumentsError")
#end
## [End] Validate key arguments. **
#if( !$util.isNull($ctx.args.gatePhone) )
  #set( $modelQueryExpression.expression = "#gatePhone = :gatePhone" )
  #set( $modelQueryExpression.expressionNames = {
  "#gatePhone": "gatePhone"
} )
  #set( $modelQueryExpression.expressionValues = {
  ":gatePhone": {
      "S": "$ctx.args.gatePhone"
  }
} )

It's looking to see if you set the partition key. After that, it goes only to check if you passed a sort key, and then how you want to sort, etc. What it doesn't do is look to see if you are querying on the GSI that was created automatically when you setup a 1-to-many relationship, and instead do a query on that.

On a large table, this would be very wasteful and expensive to do a scan all the time when a developer thought they had passed sufficient information to do a query.

[jpangburn]

The next question may be "what if you explicitly specify owner as a secondary index on the GateCodes model?" like .secondaryIndexes((index) => [index("owner")]) so the GateCodes model becomes:

GateCodes: a
  .model({
    gatePhone: a.string().required(),
    ownerCode: a.string().required(),
    name: a.string().required(),
    // create a reference field to the account (there must be an account to have a GateCodes entry)
    owner: a.string().required(),
    // use that reference field to make a belongsTo relationship
    account: a.belongsTo('Account', 'owner'),
  }).identifier(['gatePhone', 'ownerCode'])
  .secondaryIndexes((index) => [index("owner")])
  .authorization(allow => [allow.owner()]),

But it doesn't change anything. When you get the Account then ask it to get the gateCodes await data?.gateCodes() it still does a scan. When you run the observeQuery with the filter on owner it still does a scan.

The only thing you get is that you can now do this.client.models.GateCodes.listGateCodesByOwner, which is helpful, but there are still two large promises of AppSync unfulfilled (IMHO):

1. Can't efficiently load the GateCodes from an Account query/observation
2. Can't efficiently observe by owner with something like this.client.models.GateCodes.observeGateCodesByOwner().subscribe(...

[ykethan]

Hey👋 thanks for raising this! I'm going to transfer this over to our API repository for better assistance 🙂

[MattWlodarski]

I just wanted to add a couple other notes on these auto-generated GSI. First, you cannot use these as queries in your app code. They don't appear in your client after you set client = generateClient<Schema>(); You would need to create another GSI with the same primary key using @secondaryIndexes(). This is a waste because now you have 2 GSI with the same primary key. Also, the auto-generated GSI doesn't have a sort key by default and I couldn't find any information on how to set one in the Gen 2 documentation. In Gen 1 you could use the @key directive to create uni-directional relationships so this wasn't an issue.

[jpangburn]

This is a waste because now you have 2 GSI with the same primary key. Also, the auto-generated GSI doesn't have a sort key by default and I couldn't find any information on how to set one in the Gen 2 documentation.

@MattWlodarski Good points! I had tried that in my second comment and saw that afterwards I was able to see the new listBy... method in my client, but didn't realize it was creating a second GSI. Checked my DB and it did indeed. On a write heavy application, this would incur unnecessary extra cost.

I didn't need a sort key in my situation, but you're right, there doesn't appear to be a way to do that. I tried .secondaryIndexes((index) => [index(["owner", "name"])]), thinking it might be setup like .identifier(['gatePhone', 'ownerId']) where it can take a two element array. Doesn't compile- it doesn't accept an array.

I'm really wondering what the benefit is for establishing these relationships in the model? Do you know? I thought:

• simple/efficient query of child objects (half true, it is simple at least but you'd be crazy to use it in a large production application because it would eat read capacity with scans)
• delete the parent object in a one-to-many and it'll automatically delete the children for you to avoid orphaned items in your database (nope, doesn't do this- I tried deleting an account and it just left the children in the DB)

[MattWlodarski]

Hey @jpangburn, if they are scanning for the child objects instead of using the auto-generated secondary index then there is definitely no point in creating a bi-directional relationship. You would be better off just creating your own secondary index and querying for it manually in your code when needed. The whole point is to be able to pull all the data you need in one single query but scanning will make it way too slow and costly.

In Gen 1 you had to handle deleting the children yourself when necessary. There was never any auto-delete feature and I haven't seen anything in the Gen 2 documentation that says they added this feature. This would be especially nice for many to many relationships because you have to manage a linking table on top of the two parent tables. If you delete a parent but forget to delete all of the references to that parent in the linking table, you will start getting errors with any query that attempts to pull the deleted parent through the linking table.

[jpangburn]

Stumbled on the GSI sort keys documentation today! It's at https://docs.amplify.aws/angular/build-a-backend/data/data-modeling/secondary-index/#add-sort-keys-to-secondary-indexes. Copying here for anyone who came here looking for this and missed it in the documentation:

export const schema = a.schema({
  Customer: a
    .model({
      name: a.string(),
      phoneNumber: a.phone(),
      accountRepresentativeId: a.id().required(),
    })
    .secondaryIndexes((index) => [
      index("accountRepresentativeId")
        .sortKeys(["name"]),
    ])
    .authorization(allow => [allow.owner()]),
});

It creates new listBy... queries for these.

This said, still the main problem remains: GraphQL generated resolvers are ignoring GSI in 1-to-many relationship list calls, doing table SCAN instead of QUERY.

To workaround this, I ended up just dropping the 1-to-many relationship between these models. This way you can query on the GSI for your child model directly. It's an extra step in code, which is a bummer because it would be really clean to have the parent model instance and just ask for the children- but to pay for a scan instead of a query, it's a bad trade in terms of price/performance you'll pay.

[dpilch]

I see two possible solutions to this issue.

1. Update the list query to account for automatically generated GSIs when constructing the modelQueryExpression.
2. Create a listBy query for the automatically generated GSIs and update the client to use this query for lazy loading.

The team will need to discuss internally to decide the path forward.

[OperationalFallacy]

@jpangburn thanks for explaining the issue. Commented to see how Amplify going to fix this.