[BUG] node-postgres query submit preparation takes a huge amount of time

[phosmium]

Hello, there seems to be something wrong with the preparation of statement values. I'm experiencing weird unusual timings on example projects. Passing any values to ClientBase.query will cause very high timings, not doing so and just passing a statement has normal expected timings.

It seems to be caused by this code flow:

node-postgres/packages/pg/lib/query.js

Line 190 in 0096856

prepare(connection) {

PoC

(async () => {
  const client = new Client({
    user: 'postgres',
    password: 'localserver',
    database: 'test2'
  })
  await client.connect()
  const query = {
    name: 'test',
    text: 'SELECT * FROM post WHERE id = $1::integer',
    values: [1],
  }
  for (let i = 0; i < 10; i++) {
    console.time(i.toString());
    const res = await client.query(query)
    console.log(res.rows[0])
    console.timeEnd(i.toString());
  }
  await client.end()
})();

Result:

{ id: 1, ... }
0: 48.546ms
{ id: 1, ... }
1: 48.608ms
{ id: 1, ... }
2: 49.924ms
{ id: 1, ... }
3: 49.809ms
{ id: 1, ... }
4: 49.847ms
{ id: 1, ... }
5: 49.998ms
{ id: 1, ... }
6: 49.989ms
{ id: 1, ... }
7: 50.019ms
{ id: 1, ... }
8: 49.912ms
{ id: 1, ... }
9: 49.815ms

[charmander]

Please provide details about your environment (Node version, pg version, OS, are there containers/virtualization involved – e.g. Docker or WSL on either the Node or Postgres side). It’d be helpful to include the “just passing a statement” statement and timings for comparison, too, as well as timings using other client libraries.

And try SELECT $1 so it doesn’t depend on a schema or database contents.

[brianc]

This is most likely related to latency in your environment. Can you reproduce this entirely locally & demostrate the speed difference? 48 milliseconds for a single query is incredibly slow considering I get sub millisecond response times locally:

I'm running this code connecting on an m1 macbook pro to a local instance of postgres 14 (postgres.app for the win):

import { Client } from 'pg'

(async () => {
  const client = new Client()
  await client.connect()
  const query = {
    name: 'test',
    text: 'SELECT $1',
    values: [1],
  }
  for (let i = 0; i < 10; i++) {
    console.time('query');
    const res = await client.query(query)
    console.timeEnd('query');
  }
  await client.end()
})();

and this is the output

[1.17ms] query
[0.36ms] query
[0.19ms] query
[0.27ms] query
[0.16ms] query
[0.16ms] query
[0.14ms] query
[0.19ms] query
[0.26ms] query
[0.17ms] query