[BUG]: Horrible query performance

[francois-egner]

What version of drizzle-orm are you using?

0.33.0

What version of drizzle-kit are you using?

0.24.2

Describe the Bug

I am trying to run a simple aggregation query to count the existence of a record. The table to run the query against has only one entry. Querying against this table takes at least 40ms which is waaaaay to slow. Running the same query via psql or even the underlying pg-driver brings up query times around 1ms. This is the code I used to compare raw pg querying vs drizzle orm:

const pg = this.database.getInternalClient()
const drizzle = this.database.client

console.time("pg benchmark")
const statement = `select count(*) as count from matcher.swipes where id = '${id}'`;
const result = await pg.query(statement)
console.timeEnd("pg benchmark") //-> 1ms

console.time("drizzle benchmark")
const existsPrepared = drizzle
  .select({count: count()})
  .from(swipes)
  .where(eq(swipes.id, sql.placeholder('id')))
  .prepare("checkIfSwipeExistsById");

const existsResult = await existsPrepared.execute({id})
console.timeEnd("drizzle benchmark") //-> 40-50ms

Running the same raw query using drizzle.execute(...) results in the same 40-50ms of execution time. I cannot explain why drizzle makes is so damn slow. Those times are not acceptable.

Expected behavior

I expect the query time to be somewhat close to the raw pg-driver performance.

Environment & setup

• Node.js v20.17.0
• AMD Ryzen 9 7950X 16-Core Processor 4.50 GHz
• 64GB DDR5 RAM
• Windows 11 Pro

[francois-egner]

I had a look at the code and tried to follow timings. I dont know why but thel query-call of

const result = await import_tracing.tracer.startActiveSpan("drizzle.driver.execute", (span) => {
        span?.setAttributes({
          "drizzle.query.name": query.name,
          "drizzle.query.text": query.text,
          "drizzle.query.params": JSON.stringify(params)
        });
        return client.query(query, params);
      });

Is the last drizzle-code building on top of the pg module. client.query is from the pg module and adds the huge delay. So I am not sure if this is actually a drizzle problem per se. I am now concerned why this is a problem on my end but not on others.

[francois-egner]

This is actually a problem with the pg module I am using as the driver for drizzle. Version 8.1.0 of this module is the last one that results in the same performance.

[francois-egner]

Turns out there is another fix/workaround: Using Node v16 and the newest node-postgres (8.13.0) runs fine too.

[L-Mario564]

@francois-egner If it's an issue specific to the driver, it may be better to open an issue in the pg driver repo and close this one.

[francois-egner]

@L-Mario564 I already did. For whoever is interested: https://github.com/brianc/node-postgres/issues/3325 Will close this issue.