SeaQL/sea-query#720

main.rs

use postgres::{Client, NoTls};
use rand::{
    distributions::{Alphanumeric, DistString},
    Rng,
};
use sea_query::*;
use sea_query_postgres::PostgresBinder;
use std::{hint::black_box, time::Instant};

#[derive(Iden)]
enum Main {
Table,
    Id,
    Foo,
    Baz,
    Secondary,
}

#[derive(Iden)]
enum Secondary {
    Table,
    Id,
    Bar,
}

fn main() -> Result<(), postgres::Error> {
    let mut rng = rand::thread_rng();

    eprintln!("Connecting...");
    let mut client = Client::connect("host=127.0.0.1 user=test password=test", NoTls)?;

    eprintln!("Connected, inserting data...");
    {
        let mut tx = client.transaction()?;

        tx.batch_execute(
            "
            DROP TABLE IF EXISTS main;
            DROP TABLE IF EXISTS secondary;

            CREATE TABLE secondary (
                id         SERIAL PRIMARY KEY,
                bar        INTEGER
            );

            CREATE TABLE main (
                id         SERIAL PRIMARY KEY,
                foo        INTEGER,
                baz        TEXT,
                secondary  INTEGER REFERENCES secondary(id)
            );
        ",
        )?;

        for _ in 0..50000 {
            let bar: i32 = rng.gen_range(1..100);
            tx.execute("INSERT INTO secondary (bar) VALUES ($1)", &[&bar])?;
        }

        for _ in 0..50000 {
            let foo: i32 = rng.gen_range(0..1000);
            let secondary: i32 = rng.gen_range(1..50000);
            let baz = Alphanumeric.sample_string(&mut rand::thread_rng(), 16);

            tx.execute(
                "INSERT INTO main (foo, secondary, baz) VALUES ($1, $2, $3)",
                &[&foo, &secondary, &baz],
            )?;
        }
        tx.commit()?;
    }

    eprintln!("Data loaded, querying...");

    let mut inline_time = 0.0;
    let mut bind_time = 0.0;

    for i in 0..1000 {
        let inline = i & 1 == 0;
        let baz = format!(
            "{}%",
            Alphanumeric.sample_string(&mut rand::thread_rng(), 3)
        );
        let mut query = Query::select()
            .column((Main::Table, Main::Baz))
            .column((Secondary::Table, Secondary::Bar))
            .from(Main::Table)
            .left_join(
                Secondary::Table,
                Expr::col((Main::Table, Main::Secondary)).equals((Secondary::Table, Secondary::Id)),
            )
            .and_where(Expr::col((Secondary::Table, Secondary::Bar)).eq(rng.gen_range(1..100)))
            .and_where(Expr::col((Main::Table, Main::Foo)).ne(rng.gen_range(1..1000)))
            .and_where(Expr::col((Main::Table, Main::Baz)).like(baz))
            .order_by((Main::Table, Main::Id), Order::Desc)
            .take();

        if !inline {
            query.limit(1);
        }

        let (mut sql, bind) = query.build_postgres(PostgresQueryBuilder);

        if inline {
            sql = format!("{sql} LIMIT 1");
        }

        let start = Instant::now();
        for row in client.query(&sql, &bind.as_params())? {
            let _ = black_box(row.try_get::<_, String>(0));
            let _ = black_box(row.try_get::<_, i32>(1));
        }
        let duration = (start.elapsed().as_micros() as f64) / 1000.0;

        if inline {
            inline_time += duration;
        } else {
            bind_time += duration;
        }
    }

    eprintln!("inline: {inline_time:.2}ms, bind: {bind_time:.2}ms");

    Ok(())
}