SQLx is significantly slower than postgres / tokio-postgres

[Mivik]

Bug Description

I've been using sqlx for a long time until recently I discovered its performance is really, really bad. I did a benchmark on my laptop and it yielded a ridiculous 150x speed difference with regards to tokio-postgres.

Benchmark code

Things I've tried:

• Using a single connection instead of connection pool: That sped up around 2x, but a 70x difference still exists.
• Using sqlx::query!: Nothing changed.
• Enabling / disabling TLS: Nothing changed.

Info

• SQLx version: 0.6.3
• SQLx features enabled: ["runtime-tokio-native-tls", "postgres", "time", "chrono"]
• Database server and version: Postgres 15.2 (Ubuntu 15.2-1.pgdg22.04+1)
• Operating system: Ubuntu 22.04.2 LTS
• rustc --version: rustc 1.68.0 (2c8cc3432 2023-03-06)

[paolobarbolini]

Could you also try with 0.7.0-alpha.2 or with the main branch?
There have been many changes in 0.7 and some of them could also be performance improvements

[Mivik]

Could you also try with 0.7.0-alpha.2 or with the main branch? There have been many changes in 0.7 and some of them could also be performance improvements

Tried both but nothing changed.

c.bench_function("sqlx", |b| {
    let opt = DATABASE_URL
        .parse::<PgConnectOptions>()
        .unwrap()
        .disable_statement_logging();
    let rt = Runtime::new().unwrap();
    let db = rt
        .block_on(PgPoolOptions::new().max_connections(50).connect_with(opt))
        .unwrap();
    b.to_async(rt).iter(|| async {
        sqlx::query("select db_id from lc_map where lc_id = $1")
            .bind(rand_token())
            .fetch_optional(&db)
            .await
            .unwrap();
    });
});

[frederikhors]

Wow. This is tragic! There will be some mistake somewhere.

[FSMaxB]

I would expect this to be related with connection query pipelining (which tokio-postgres does by default AFAIK).
See also #408

[Mivik]

I found the cause. My table schema is:

create table lc_map(
  lc_id char(24) primary key,
  db_id int not null
);

To execute the statement (fetch_optional), both SQLx and tokio-postgres have to fetch parameter types from the server. The difference is, SQLx specifies the parameter type to be TEXT (from here), while tokio-postgres specifies nothing.

As a result, the parameter type returned from the server is still TEXT for SQLx, but is BPCHAR for tokio-postgres. Then, in the encoding phase, SQLx encodes the String as TEXT while tokio-postgres encodes it as BPCHAR. For some reason, using TEXT as encoding format is significantly slower than BPCHAR.

To prove that this is the reason, I specified BPCHAR instead of TEXT in SQLx and then everything worked fine.

If we stop specifying the parameter types then the problem shall be resolved. But I'm not sure if that's the best solution.

[frederikhors]

then everything worked fine

So? The results with this edit?

[Mivik]

So? The results with this edit?

I'd say a fairly high portion of the time SQLx takes still comes from the connection pool, as I mentioned in the issue, but I had difficulties writing a single connection version (it panics with this functionality requires a Tokio context)

[Mivik]

Changing the column type from CHAR(24) to TEXT or VARCHAR(24) also significantly reduces the time, though being slightly slower than the edit above (~200μs)

[Mivik]

Okay. I checked the query plans for select * from lc_map where lc_id = 'id'::text and select * from lc_map where lc_id = 'id'::char(24), and found that the former one uses a SeqScan while the later one uses IndexScan. That's the root cause.

[abonander]

I fail to see how this is a SQLx bug, honestly. Even the Postgres manual recommends against using the char(N) type: https://www.postgresql.org/docs/current/datatype-character.html

While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Theoretically it should still be able to perform an index scan with a text value, but this is obviously triggering some sort of pessimization in the query planner because it doesn't think the types should be compatible. It's worth checking if this is a known bug in Postgres, because the char(N) type is probably not used all that much.

I'd say a fairly high portion of the time SQLx takes still comes from the connection pool, as I mentioned in the issue, but I had difficulties writing a single connection version (it panics with this functionality requires a Tokio context)

That's because SQLx executes I/O in the same async task where it's invoked while tokio-postgres spawns its own task for managing the connection and communicates via channels, so it cares less about the actual async context where it's invoked as long as the Tokio runtime it started on is still running. If the code was similar to what you posted, I don't see why there would be an issue as Criterion should be using the same Tokio runtime for both.

I've thought about changing to the same execution strategy as tokio-postgres which would fix a number of issues and also allow pipelining, but that's a non-trivial refactor.