PostgreSQL database index not being used when using `bind` with wrong type

[timhabermaas]

Disclaimer: This is very likely a user error on my part, but I believe creating this issue has value to a) discuss whether sqlx can help prevent it in the future or mention it in the documentation and b) improve search results for the next person running into it (the only thing I could find was this 17 year old thread: https://www.postgresql.org/message-id/1067104140.16297.125.camel@tallac).

Given the following table and index:

CREATE TABLE tweet 
  ( 
     id         BIGSERIAL PRIMARY KEY, 
     created_at TIMESTAMPTZ NOT NULL DEFAULT Now(), 
     text       TEXT NOT NULL, 
     owner_id   BIGINT 
  ) 
CREATE INDEX index_owner_id_on_tweet 
  ON tweet (owner_id); 

This query will use the index and will execute reasonably fast:

    let _ = sqlx::query("SELECT text from tweet WHERE owner_id = $1")
        .bind(2_i32)
        .fetch_all(&pool)
        .await;

This query will not use the index and therefore (often) execute slower:

    let _ = sqlx::query("SELECT text from tweet WHERE owner_id = $1")
        .bind(2_u32)
        .fetch_all(&pool)
        .await;

The only difference between the two is the type provided to bind: i32 vs u32.

The reason why these queries behave differently is that parameters in

sqlx/sqlx-core/src/postgres/connection/executor.rs

Lines 22 to 27 in 31abe22

	async fn prepare(
	conn: &mut PgConnection,
	sql: &str,
	parameters: &[PgTypeInfo],
	metadata: Option<Arc<PgStatementMetadata>>,
	) -> Result<(u32, Arc<PgStatementMetadata>), Error> {

contains [PgTypeInfo(Oid)] for bind(2_u32) and [PgTypeInfo(Int4)] for bind(2_i32) which (I assume) will lead to some casting on the Postgres side of things which will prevent it from using an index (very old thread which might still be relevant).

Note: There's still a measurable runtime difference between the two if no index is present: The i32 variant is still faster, but not by an order of magnitude.

This is not an issue when using the compile-time verification of SQL statements (query! macro). The compiler will complain about i32 != u32.

Reproduction repo

https://github.com/timhabermaas/sqlx-no-index-use-reproduction contains a minimal setup to reproduce the issue. A setup guide can be found in the README.md.

Versions which showed this behaviour

• PostgreSQL: 13.1 and 9.6.20
• sqlx: 0.4.2
• Rust: 1.49.0

[timhabermaas]

One possible fix is to map u32 to PgType::Int8 instead of to PgType::Oid, but I don't know if that's a reasonable thing to do.