Very slow query with a simple schema and simple query

[flodlc]

Hi, thank you for your work and last versions that enabled many awesome features.
My only concern with Kysely is that the typechecking is very slow in my large codebase and 80 tables.

I tried to reduce a slow query to identify patterns that leads to slow typechecking.

Here is a version (joins conditions make absolutely no sense in the exemple).
As you can see, the query in not very complexe, the schema neither. But it still takes around 3.09s to run (1.26s check time).

If I remove the 2 select statements, check time goes down to 0.15s which is way better. It seems that the coalesce hurts the performance too but I struggle to find a single reason, sometimes removing a single join reduce check time by 4.
Is it a "normal" performance for you or should I keep investigate?

The whole repo that contains hundreds of queries (simple and complexe) need 120s to typecheck.
I had lot of hope with the annoucement of ts-go but it only reduce by 3 the check time and increase the memory (almost 8gb).

Spec:
I run Kysely version 28.3 on Macbook pro m2 16gb ram.
Typescript 5.9

{
  "include": ["src/db/getCart.query.ts"],
  "compilerOptions": {
    "exactOptionalPropertyTypes": false,
    "isolatedModules": true,
    "moduleResolution": "node",
    "resolveJsonModule": true,
    "target": "ES2020",
    "strict": true,
    "skipLibCheck": true,
    "declaration": true,
    "jsx": "react-jsx",
  },
}

Files:              934
Lines:          1008736
Identifiers:    1006670
Symbols:         609668
Types:            23058
Instantiations:  291886
Memory used:    827969K
I/O read:         0.21s
I/O write:        0.00s
Parse time:       1.29s
Bind time:        0.51s
Check time:       1.26s
Emit time:        0.00s
Total time:       3.09s

import { ColumnType, Kysely, PostgresDialect } from "kysely";
import { jsonArrayFrom, jsonBuildObject } from "kysely/helpers/postgres";
import { Pool } from "pg";

export type AutomationRunStatus = "failed" | "pending" | "success";

type Generated<T> =
  T extends ColumnType<infer S, infer I, infer U>
    ? ColumnType<S, I | undefined, U>
    : ColumnType<T, T | undefined, T>;

type Json = JsonValue;

type JsonArray = JsonValue[];

type JsonObject = {
  [x: string]: JsonValue | undefined;
};

type JsonPrimitive = boolean | number | string | null;

type JsonValue = JsonArray | JsonObject | JsonPrimitive;

type Numeric = ColumnType<number, number | string, number | string>;

type Timestamp = ColumnType<string, Date | string, Date | string>;

interface Cart {
  amount: Generated<number>;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  currency: string;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  expirationDate: Timestamp | null;
  groupId: string;
  hasPayments: Generated<boolean>;
  hasRefunds: Generated<boolean>;
  id: Generated<string>;
  invoiced: Generated<boolean>;
  netAmount: number;
  paidAmount: Generated<number>;
  paymentRequestedById: string | null;
  paymentRequestedOn: Timestamp | null;
  reference: string;
  sessionId: string | null;
  status: string;
  targetCartId: string | null;
  token: string;
  toProcess: Generated<boolean>;
  totalAmount: number;
  type: string;
  updatedOn: Generated<Timestamp>;
  userId: string;
  vatRate: number | null;
}

interface CartCartItem {
  cartId: string;
  cartItemId: string;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  id: Generated<string>;
  sessionId: string | null;
  updatedOn: Generated<Timestamp>;
}

interface CartItem {
  amount: number | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  currency: string | null;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  entryId: string | null;
  eventIndex: string | null;
  expiresOn: Timestamp | null;
  id: Generated<string>;
  label: string | null;
  manualPrice: Generated<boolean>;
  netAmount: number | null;
  planItemId: string | null;
  productId: string;
  sessionId: string | null;
  standaloneUsedOn: Timestamp | null;
  totalAmount: number | null;
  type: string;
  typeId: string | null;
  updatedOn: Generated<Timestamp>;
  used: Generated<boolean>;
  userId: string;
  vatOnTop: Generated<boolean>;
  vatRate: Numeric | null;
}

interface Event {
  alertId: string | null;
  cap: number | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  description: string | null;
  endsOn: Timestamp;
  groupId: string;
  id: Generated<string>;
  importedEventId: string | null;
  location: string | null;
  originalEventId: string | null;
  photoId: string | null;
  privacyId: string;
  published: Generated<boolean>;
  recurrentEventId: string | null;
  registrationPrivacyId: string;
  repeat: string | null;
  serviceId: string | null;
  sessionId: string | null;
  startsOn: Timestamp;
  title: string | null;
  updatedOn: Generated<Timestamp>;
}

interface EventParticipant {
  alertSent: Generated<boolean>;
  alertSentOn: Timestamp | null;
  cancelledOn: Timestamp | null;
  code: string | null;
  confirmationMessage: string | null;
  confirmedOn: Timestamp | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  eventId: string;
  followUp2Sent: Generated<boolean>;
  followUp2SentOn: Timestamp | null;
  followUpSent: Generated<boolean>;
  followUpSentOn: Timestamp | null;
  host: Generated<boolean>;
  id: Generated<string>;
  invitedOn: Timestamp | null;
  nbNotifications: Generated<number>;
  registeredOn: Timestamp | null;
  sessionId: string | null;
  status: string;
  updatedOn: Generated<Timestamp>;
  userId: string;
  waitingNotifSentOn: Timestamp | null;
}

interface EventParticipantTicket {
  bottomBannerFileId: string | null;
  cartItemId: string | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  description: string | null;
  eventParticipantId: string;
  id: Generated<string>;
  qrCode: string | null;
  sessionId: string | null;
  status: string;
  ticketFileId: string | null;
  topBannerFileId: string | null;
  updatedOn: Generated<Timestamp>;
}

interface Member {
  admin: Generated<boolean>;
  billingAddress: string | null;
  billingName: string | null;
  billingSiret: string | null;
  birthDate: string | null;
  contactMessage: string | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  customFields: Record<string, any> | null;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  email: string | null;
  fakeId: string | null;
  feedNotif: Generated<boolean>;
  firstName: string | null;
  gender: string | null;
  groupId: string;
  id: Generated<string>;
  includesExternalEvents: Generated<boolean>;
  isMember: Generated<boolean>;
  lastName: string | null;
  lastViewedOn: Timestamp | null;
  mobilePhone: string | null;
  note: string | null;
  notifSettings: Generated<Json>;
  permissions: Generated<string[]>;
  photoId: string | null;
  prospect: Generated<boolean>;
  prospectViewedOn: Timestamp | null;
  responsibleMemberId: string | null;
  sessionId: string | null;
  stripeCustomerId: string | null;
  targetMemberId: string | null;
  updatedOn: Generated<Timestamp>;
  userId: string;
  visible: Generated<boolean>;
}

interface Product {
  name: string;
  order: number | null;
  paymentPolicies: string[];
  planType: string;
  price: number | null;
  privacyId: string | null;
  published: Generated<boolean>;
  recurrenceDuration: number | null;
  recurrenceOnStartOfPeriod: boolean | null;
  recurrenceTime: number | null;
  recurrenceUnit: "day" | "week" | "month" | "year" | null;
  registrationEmailTemplate: string | null;
  registrationPrivacyId: string | null;
  registrationPrivacyMessage: string | null;
  reservationMode: string;
  sessionId: string | null;
  shortDescription: string | null;
  slotGranularity: number | null;
  startsOn: Timestamp | null;
  ticketBottomBannerFileId: string | null;
  ticketDescription: string | null;
  ticketTopBannerFileId: string | null;
  timeBefore: number | null;
  timeOfTheDay: string | null;
  titleFormat: string | null;
  type: string;
  updatedOn: Generated<Timestamp>;
  vatOnTop: Generated<boolean>;
}

interface Registration {
  cartId: string | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  customFields: Record<string, any>;
  id: Generated<string>;
  productId: string;
  sessionId: string | null;
  updatedOn: Generated<Timestamp>;
}

interface User {
  ageRange: string | null;
  bio: string | null;
  birthDate: string | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  email: string | null;
  firstName: string | null;
  gender: string | null;
  id: Generated<string>;
  language: string | null;
  lastName: string | null;
  links: string[] | null;
  mobilePhone: string | null;
  photoId: string | null;
  sessionId: string | null;
  stripeCustomerId: string | null;
  superAdmin: Generated<boolean>;
  targetUserId: string | null;
  updatedOn: Generated<Timestamp>;
}

export interface Group {
  catalog: Generated<boolean>;
  categories: Generated<string[]>;
  category: Generated<string>;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  currency: string | null;
  daysBeforePaymentDue: Generated<number | null>;
  dealsEnabled: Generated<boolean>;
  dealsMediaId: string | null;
  defaultAlertId: string | null;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  description: string | null;
  displayMembers: Generated<boolean>;
  email: string | null;
  eventFullMessage: string | null;
  groupName: string;
  id: Generated<string>;
  language: Generated<string>;
  location: string | null;
  membershipEnabled: Generated<boolean>;
  notifyMessage: string | null;
  onlinePaymentEnabled: Generated<boolean>;
  pastDueMessage: string | null;
  paymentPolicy: Generated<string>;
  phoneNumber: string | null;
  photoId: string | null;
  plan: Generated<string>;
  postPermission: Generated<string>;
  published: Generated<boolean>;
}

export interface PlanItem {
  acronym: string | null;
  cartItemId: string;
  comment: string | null;
  createdById: string | null;
  createdOn: Generated<Timestamp>;
  credits: number | null;
  customFields: Json | null;
  deleted: Generated<boolean>;
  deletedById: string | null;
  deletedOn: Timestamp | null;
  eventsSetId: string | null;
  expirationDate: Timestamp | null;
  id: Generated<string>;
  name: string;
  planItemRecurrenceId: string | null;
  remainingAmount: number | null;
  remainingCredits: number | null;
  sessionId: string | null;
  startDate: Timestamp | null;
  status: string;
  type: string;
  updatedOn: Generated<Timestamp>;
  userId: string;
}

export interface DB {
  cart: Cart;
  cartCartItem: CartCartItem;
  cartItem: CartItem;
  eventParticipant: EventParticipant;
  event: Event;
  eventParticipantTicket: EventParticipantTicket;
  member: Member;
  product: Product;
  registration: Registration;
  user: User;
  group: Group;
  planItem: PlanItem;
}

const db = new Kysely<DB>({
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: "" }) }),
});

export const findCartQuery = async () => {
  const result = await db
    .selectFrom("cart")
    .leftJoin("cartCartItem", "cart.id", "cartCartItem.cartId")
    .leftJoin("cartItem", "cartItem.id", "cartCartItem.cartItemId")
    .leftJoin("eventParticipantTicket", "eventParticipantTicket.cartItemId", "cartItem.id")
    .leftJoin(
      "eventParticipant",
      "eventParticipant.id",
      "eventParticipantTicket.eventParticipantId"
    )
    .leftJoin("event", "event.id", "eventParticipant.eventId")
    .leftJoin("planItem", "planItem.id", "event.id")
    .leftJoin("user as user_owner", "user_owner.ageRange", "event.cap")
    .leftJoin("member as owner_member", "owner_member.userId", "user_owner.id")
    .leftJoin("product", "cartItem.productId", "product.name")
    .leftJoin("group", "group.id", "product.name")
    .leftJoin("user", (j) => j.onRef("user.id", "=", "cart.userId"))
    .select((eb) =>
      jsonArrayFrom(
        eb
          .selectFrom("registration")
          .innerJoin("member", (j) => j.on("member.id", "=", "test"))
          .select(
            jsonBuildObject({
              firstName: eb.ref("owner_member.firstName"),
              lastName: eb.ref("owner_member.lastName"),
            }).as("owner")
          )
      ).as("registrations")
    )
    .select((eb) =>
      jsonArrayFrom(
        jsonBuildObject({
          updatedOn: eb.fn.coalesce("cart.updatedOn", "cart.createdOn"),
          toto: eb.fn.coalesce("cart.amount", "cart.totalAmount"),
        })
      ).as("cartItems")
    )
    .execute();

  return result;
};

[igalklebanov]

Hey 👋

Thanks for providing details.

Can you provide a reproduction repository?

This query seems quite overly complicated - smells like it needs a full rewrite. Can you provide the result shape you want out of this query?

Does it happen in 0.28.2 too?

[flodlc]

Yes, this query is an extract of a bigger query that I modified to try to identify pattterns, it's not supposed to make sense 😄
I'll make a repo, now. Does 1s check time seems a lot for you for a single queery ? What time would you expect to be acceptable ?

[igalklebanov]

We strive to make everything as fast as possible. Am I happy with any of this? nope. I don't have any data to say if it's normal or not.

[flodlc]

Here is the repo: https://github.com/flodlc/kysely-perf-repro
Thanks to help on this!

Edit: Yes, it was already here on 0.27. Upgrading to 0.28 decreased a bit the perf on my repo but not dramatically (around 10% slower).

[igalklebanov]

Was able to drop 1s.

1. use a single .select((eb) => [...]).
2. situational - use db instead of eb as subquery root to work with an empty query context.

[flodlc]

Thank for you help, using 2 helpers also helps. But it's still not satisfying that a single query already leads to performance issue.
I hope we will find out a magic trick to make Kysely fast in every situation. Also I think that type checking performance best practices could be a section of the doc. Would be glad to help if needed but right now I'll be a reader more than a writer of such a section.

[igalklebanov]

hypothetical feature: a eb.$pickTables<'cart'>.fn.coalesce('cart.updatedOn', 'cart.createdOn') has cut instantiations by almost half, and dropped wall clock by another 0.5s.

[flodlc]

Using db instead of eb for subqueries actually helps a lot! On a complex query, switching to db and merging all the select statements into a single one brought execution time down to 2s instead of 2.8s. In this case, splitting subqueries into helpers didn’t help at all — it might even be slightly better without splitting.

I had already considered creating a helper to reduce schema scope. I implemented one on db using a getDb helper. But I ran into type errors when passing eb as a helper parameter, since it didn’t match the expected type.

Your hypothetical feature seems more precise, since it would target only a specific portion of the query.

[flodlc]

hypothetical feature: a eb.$pickTables<'cart'>.fn.coalesce('cart.updatedOn', 'cart.createdOn') has cut instantiations by almost half, and dropped wall clock by another 0.5s.

Just tried it on my side too using expressionBuilder<Pick<DB, TableNames>, TableNames>() instead of DB and achieved descreasing check time from 1.2s to 0.6s.

Promising!

[Blitz2145]

hypothetical feature: a eb.$pickTables<'cart'>.fn.coalesce('cart.updatedOn', 'cart.createdOn') has cut instantiations by almost half, and dropped wall clock by another 0.5s.

I would love to see pickTables on eb also, since I think it would really help with TS perf on complex eb expressions inside select that I've had to convert back to sql tag for performance. Pick tables on db does still help alot though, so excited for that release.