Support is_true / is_false methods for boolean expressions

[belovaf]

Even crates.io has a lot of booleanProp.eq(true) expressions, for example:
github_secret_scanning.rs#L62
This leads to an additional bind parameter and slows down a query.
It would be very convenient (and better performance) to have functions is_true / is_false to be rendered as = true / = false.

[weiznich]

I would like to see hard numbers for the claim that additional bind parameters slow down the query. Otherwise this does not seem to be justified in any way, given that it would require us to start dealing to convert rust values to SQL string representations with is something that we really want to avoid.

[belovaf]

Sure.

I have created the simpliest benchmark (for complex queries difference is bigger).

create table my_table
(
    id        bigint generated always as identity primary key,
    is_active bool not null
);

insert into my_table(is_active)
select false
from generate_series(1, 1000000) x;

insert into my_table(is_active) values (true);

create index my_table_is_active_idx on my_table(is_active);

#[bench]
fn select_literal(b: &mut Bencher) {
    let mut con = init_connection();

    b.iter(|| {
        let count = my_table::table
            .filter(my_table::is_active.eq(BoolLiteral(true)))
            .select(count_star())
            .get_result::<i64>(&mut con)
            .unwrap();

        black_box(count);
    });
}

#[bench]
fn select_bind(b: &mut Bencher) {
    let mut con = init_connection();

    b.iter(|| {
        let count = my_table::table
            .filter(my_table::is_active.eq(true))
            .select(count_star())
            .get_result::<i64>(&mut con)
            .unwrap();
        black_box(count);
    });
}

Results:
test tests::select_bind ... bench: 725,810.26 ns/iter (+/- 266,623.54)
test tests::select_literal ... bench: 660,386.51 ns/iter (+/- 148,967.49)

It is 10% difference.

[belovaf]

For example, I have tried to repeat the same predicate 15 times for each query, the results are:
test tests::select_bind ... bench: 795,133.44 ns/iter (+/- 162,428.02)
test tests::select_literal ... bench: 688,388.66 ns/iter (+/- 158,588.53)

And this is already 15% difference.

[mjanda]

I will add that if I remember correctly, when you prepare statement with bound boolean variable, PostgreSQL cannot know if it can use partial index over that field. Can you re-run your benchmark with partial index? Thank you

[belovaf]

This is not mentioned in postgres documentation. As I see postgres uses the partial index in both cases:
test tests::select_bind ... bench: 748,491.19 ns/iter (+/- 319,222.16)
test tests::select_literal ... bench: 674,476.44 ns/iter (+/- 158,047.36)

[weiznich]

Thanks for providing these numbers. Looks like it might be a meaningful addition to diesel itself. As already written before I do currently not have the time to work on that on my own, but we would appropriate a PR for this.

I think we could go with something like that as function signature:

fn literal_value<const V: bool, DB: Backend>() -> LiteralValue<V, DB> {}

This would allow us to expand that function to also support integers and similar types in the future as soon as we can use generic types for const parameters.

Things I would expect from an implementation:

• Documentation and tests should be part of the PR
• We need to make sure that LiteralValue<true> has a different static query ID than LiteralValue<false> as otherwise the prepared statement caching breaks as soon as a user inserts both into the statement cache. (There should be a test for that)
• We need to check if the backend supports the boolean type, therefore this backend parameter in the proposed function signature.

If anyone is willing to work on this we would be happy to receive a PR. If you have problems with your local setup let me know so that I can try to help.