Why does the d1 upsert operation need to read 10k rows each time?

[rxliuli]

For Workers & Pages, what is the name of the domain?

no

What is the issue or error you’re encountering

d1 reading line count exception, very strange.

What steps have you taken to resolve the issue?

For example, a query read an average of 9,540 rows and was executed a total of 7,027 times.

{
    "query": "insert into \"User\" (\"id\", \"screenName\", \"name\", \"description\", \"profileImageUrl\", \"accountCreatedAt\", \"spamReportCount\", \"createdAt\", \"updatedAt\", \"followersCount\", \"followingCount\", \"blueVerified\", \"defaultProfile\", \"defaultProfileImage\") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) on conflict (\"User\".\"id\") do update set \"id\" = ?, \"screenName\" = ?, \"name\" = ?, \"description\" = ?, \"profileImageUrl\" = ?, \"accountCreatedAt\" = ?, \"spamReportCount\" = \"User\".\"spamReportCount\" + ?, \"followersCount\" = ?, \"blueVerified\" = ?, \"defaultProfile\" = ?, \"defaultProfileImage\" = ?",
    "avgRowsRead": 9540,
    "totalRowsRead": 67044465,
    "avgRowsWritten": 3,
    "totalRowsWritten": 21081,
    "avgDurationMs": 2.4640671837199384,
    "totalDurationMs": 17315.00010000001,
    "numberOfTimesRun": 7027,
    "queryEfficiency": 0
  }

Table schema

export const user = sqliteTable(
  'User',
  {
    id: text('id').primaryKey(),
    screenName: text('screenName').notNull(),
    name: text('name'),
    description: text('description'),
    profileImageUrl: text('profileImageUrl'),
    accountCreatedAt: text('accountCreatedAt').$defaultFn(() =>
      new Date().toISOString(),
    ),
    spamReportCount: integer('spamReportCount').default(0).notNull(),
    createdAt: text('createdAt')
      .notNull()
      .$defaultFn(() => new Date().toISOString()),
    updatedAt: text('updatedAt')
      .notNull()
      .$defaultFn(() => new Date().toISOString()),
    followersCount: integer('followersCount').default(0).notNull(),
    followingCount: integer('followingCount').default(0).notNull(),
    blueVerified: integer('blueVerified', { mode: 'boolean' })
      .default(false)
      .notNull(),
    defaultProfile: integer('defaultProfile', { mode: 'boolean' })
      .default(true)
      .notNull(),
    defaultProfileImage: integer('defaultProfileImage', {
      mode: 'boolean',
    })
      .default(true)
      .notNull(),
  },
  (table) => [index('user_spamReportCount_idx').on(table.spamReportCount)],
)

What are the steps to reproduce the issue?

I’m not quite sure how to provide the reproduction steps.

Screenshot of the error