[BUG] Slow on querying table with GSI.

[ChunAllen]

Summary:

I have table with more than 20+ columns, total table size is 1.3 MB, total item count is 291 with average item size of 4,636.77 bytes
I also implemented a GSI so I can use a query instead of scan but when I run my lambda the response time is 5-7 seconds.
I also configured the dynamodb tables to be on demand.

I tried also these two approach.

• Using dynamoose to query table - this returns 5-7 seconds
• Using DynamoDb.DocumentClient - this returns milliseconds to 2 seconds.

This is the screenshot from dynamodb console table

Code sample:

Schema

models/concerns/initDB.js

'use strict';

const dynamoose = require('dynamoose');

module.exports.init = () => {

  if (process.env.STAGE == 'local') {
    const ddb = new dynamoose.aws.ddb.DynamoDB({
      "region": process.env.REGION
    });

    dynamoose.aws.ddb.set(ddb);

    dynamoose.aws.ddb.local("http://localhost:8000");
  }
  return dynamoose;
}

models/concerns/custom_pois.js

'use strict';

const dynamoose = require('./initDB');

const Schema = dynamoose.init().Schema;

const schemaDefinition = {
  uid: {
    type: String,
    hashKey: true
  },
  version: {
    type: Number,
    rangeKey: true
  },
  tihUuid: {
    type: String,
    default: ""
  },
  name: {
    type: String,
    required: true
  },
  categoryDescription: {
    type: String,
    default: ""
  },
  archived: {
    type: Boolean,
    default: false
  },
  images: {
    type: Array,
    schema: [{
      type: Object,
      schema: {
        url: String,
        libraryUuid: String,
        primaryFileMediumUuid: String,
        uuid: String
      }
    }],
    default: []
  },
  thumbnails: {
    type: Array,
    schema: [{
      type: Object,
      schema: {
        url: String,
        libraryUuid: String,
        primaryFileMediumUuid: String,
        uuid: String
      }
    }],
    default: []
  },
  status: {
    type: String,
    index: {
      global: true,
      name: 'status_name_index',
      rangeKey: 'name',
      project: true
    },
    required: true,
  },
  body: {
    type: String,
    default: ""
  },
  initialDescription: {
    type: String,
    default: ""
  },
  description: {
    type: String,
    default: "",
  },
  shortDescription: {
    type: String,
    default: ""
  },
  poiType: {
    type: String,
    default: ""
  },
  source: {
    type: String,
    default: ""
  },
  dataset: {
    type: String,
    default: ""
  },
  address: {
    type: Object,
    schema: {
      block: String,
      streetName: String,
      floorNumber: String,
      unitNumber: String,
      buildingName: String,
      postalCode: String
    },
    default: {}
  },
  nearestMrtStation: {
    type: String,
    default: ""
  },
  companyDisplayName: {
    type: String,
    default: ""
  },
  companyName: {
    type: String,
    default: ""
  },
  contact: {
    type: Object,
    schema: {
      primaryContactNo: String,
      secondaryContactNo: String,
      otherContactNo: String
    },
    default: {}
  },
  defaultLanguage: {
    type: String,
    default: ""
  },
  eventOrganizer: {
    type: String,
    default: ""
  },
  frequencyOfTours: {
    type: String,
    default: ""
  },
  location: {
    type: Object,
    schema: {
      latitude: Number,
      longitude: Number
    },
    default: {}
  },
  officialEmail: {
    type: String,
    default: ""
  },
  officialWebsite: {
    type: String,
    default: ""
  },
  price: {
    type: String,
    default: ""
  },
  pricing: {
    type: Object,
    schema: {
      child: String,
      others: String,
      seniorCitizen: String,
      adult: String
    },
    default: {}
  },
  rating: {
    type: Number,
    default: 0.0
  },
  startingPoint: {
    type: String,
    default: ""
  },
  endingPoint: {
    type: String,
    default: ""
  },
  stories: {
    type: String,
    default: ""
  },
  supportedLanguage: {
    type: Array,
    schema: [String],
    default: ""
  },
  tags: {
    type: Array,
    schema: [String],
    default: ""
  },
  tourDuration: {
    type: String,
    default: ""
  },
  typeDescription: {
    type: String,
    default: ""
  },
  minimumAge: {
    type: String,
    default: ""
  },
  notes: {
    type: String,
    default: ""
  },
  origin: {
    type: String,
    default: ""
  },
  reviews: {
    type: Array,
    schema: [{
      type: Object,
      schema: {
        authorName: String,
        authorURL: String,
        profilePhoto: String,
        rating: [String, Number],
        text: String
      }
    }],
    default: []
  },
  businessHour: {
    type: Array,
    schema: [{
      type: Object,
      schema: {
        openTime: String,
        closeTime: String,
        daily: Boolean,
        day: String,
        description: String,
        sequenceNumber: [String, Number]
      }
    }],
    default: []
  },
  metadata: {
    type: Object,
    schema: {
      createdBy: String,
      createdDate: String,
      updatedBy: String,
      updatedDate: String
    },
    default: {}
  },
  temporarilyClosed: {
    type: String,
    default: ""
  },
  district: {
    type: String,
    default: ""
  },
  categories: {
    type: Array,
    schema: [{
      type: Object,
      schema: {
        primary: String,
        subcategory: String
      }
    }],
    default: []
  },
  advanceBookingRequired: {
    type: Boolean,
    default: false
  },
  priceRange: {
    type: String,
    default: ""
  },
  area: {
    type: String,
    default: ""
  },
  wetWeatherFriendly: {
    type: Boolean,
    default: false
  },
  reasonsToGo: {
    type: Array,
    schema: [String],
    default: []
  },
  staffTips: {
    type: String,
    default: ""
  },
  goodFor: {
    type: Array,
    schema: [String],
    default: []
  },
  accessibility: {
    type: Array,
    schema: [String],
    default: []
  },
  facilities: {
    type: Array,
    schema: [String],
    default: []
  },
  dietaryRestrictions: {
    type: Array,
    schema: [String],
    default: []
  },
  awardWinning: {
    type: Array,
    schema: [String],
    default: []
  },
  cuisine: {
    type: String,
    default: ""
  },
  typeOfCuisine: {
    type: Array,
    schema: [String],
    default: []
  },
  toursAndExperiences: {
    type: Array,
    schema: [String],
    default: []
  },
  descriptiveLabels: {
    type: Object,
    schema: {
      colors: {
        type: Array,
        schema: [String]
      },
      features: {
        type: Array,
        schema: [String]
      },
      functions: {
        type: Array,
        schema: [String]
      }
    },
    default: {}
  },
  externalLinks: {
    type: Object,
    schema: {
      chope: String
    },
    default: {}
  },
  createdAt: {
    type: Number
  },
  updatedAt: {
    type: Number
  },
  lastModifiedBy: {
    type: String
  }
}

const CustomPoiSchema = new Schema(schemaDefinition);

const CustomPoi = dynamoose.init().model('custom_pois', CustomPoiSchema, { create: false, update: false });

exports.CustomPoi = CustomPoi;

Model

const status = (event.queryStringParameters && event.queryStringParameters.status) || 'published'

  const customPois = await CustomPoi
    .query("status").eq(status)
    .sort("ascending")
    .using("status_name_index")
    .filter("archived").eq(false)
    .all()
    .exec()
    
  return customPois

General

This is my Lambda Function.

const CustomPoi = require('../../../models/concerns/custom_pois').CustomPoi

module.exports.searchPois = async (event) => {
  const status = (event.queryStringParameters && event.queryStringParameters.status) || 'published'

  const customPois = await CustomPoi
    .query("status").eq(status)
    .sort("ascending")
    .using("status_name_index")
    .filter("archived").eq(false)
    .all()
    .exec()
    
  return customPois
 }

serverless.yml

CustomPoisDynamoDBTable:
  Type: "AWS::DynamoDB::Table"
  Properties:
    BillingMode: PAY_PER_REQUEST
    AttributeDefinitions:
      - AttributeName: uid
        AttributeType: S
      - AttributeName: version
        AttributeType: N
      - AttributeName: status
        AttributeType: S
      - AttributeName: name
        AttributeType: S
    KeySchema:
      - AttributeName: uid
        KeyType: HASH
      - AttributeName: version
        KeyType: RANGE
    TableName: custom_pois
    GlobalSecondaryIndexes:
      - IndexName: "status_name_index"
        KeySchema:
          - AttributeName: status
            KeyType: HASH
          - AttributeName: name
            KeyType: RANGE
        Projection:
          ProjectionType: ALL

Current output and behavior (including stack trace):

I'm expecting that adding GSI and using query should be faster

Expected output and behavior:

• I'm expecting that dynamoose query should be faster since I added already a GSI.
• I tried using the built in Dynamodb Document Client query and it's faster.

Environment:

Operating System:
Operating System Version: Apple M1
Node.js version (node -v): 16.18
NPM version: (npm -v): 8.19.2
Dynamoose version: 3.1.0

Other information (if applicable):

I also tried using the normal dynamodb document client query using the syntax

const CustomPoi = require('../../../models/concerns/custom_pois').CustomPoi
const AWS = require('aws-sdk')
const dynamodb = new AWS.DynamoDB.DocumentClient()

module.exports.searchPois = async (event) => {
  const params = {
    TableName: "custom_pois",
    IndexName: "status_name_index",
    KeyConditionExpression: "#status = :status_val",
    ExpressionAttributeNames: {
      "#status": "status",
      "#archived": "archived"
    },
    ExpressionAttributeValues: {
      ":status_val": status,
      ":archived_val": false
    },
    FilterExpression: "#archived = :archived_val",
    ScanIndexForward: true // sort key for GSI is ascending
  }

  const data = await dynamodb.query(params).promise()
  return data.Items
 }

Other:

• I have read through the Dynamoose documentation before posting this issue
• I have searched through the GitHub issues (including closed issues) and pull requests to ensure this issue has not already been raised before
• I have searched the internet and Stack Overflow to ensure this issue hasn't been raised or answered before
• I have tested the code provided and am confident it doesn't work as intended
• I have filled out all fields above
• I am running the latest version of Dynamoose

[Josefnm]

Could it be the same as this?
#1260