Low performance of query_iter

[hombit]

I have a table with 7 columns and ~ 10 million of rows. I rewrote my python code that selects the entire table to rust and noticed that rust version is much slower.

Rust code

use postgres::{Client, NoTls};
use fallible_iterator::FallibleIterator;


fn main() {
    let mut client = Client::connect("host=/var/run/postgresql user=user", NoTls).unwrap();
    let mut it = client.query_iter("SELECT * FROM my_table", &[]).unwrap();
    while let Some(_row) = it.next().unwrap() {
    }
}

time:

204.36s user 184.91s system 238% cpu 2:43.22 total

Python code

#!/usr/bin/env python3


import psycopg2


def main():
    with psycopg2.connect("user='user'") as con, con.cursor() as cur:
        cur = con.cursor()
        cur.execute('SELECT * FROM my_table;')
        for row in cur:
            pass


if __name__ == '__main__':
    main()

time:

14.60s user 2.48s system 43% cpu 39.215 total

[sfackler]

Just to make sure, you build the Rust code with --release, right?

I'm betting this is overhead from shipping each of the 10 million rows separately over a channel. We should be able to start batching a bit to speed things up.

[hombit]

Yes, I’ve used release flag.

I’m not sure that the reason only in fetch size: 1) I tried to set psycopg2 cursor.itersize to unity in Python (it should reduce batch size to unity), the execution time increased up to 50 seconds only, and 2) Rust code uses a lot of CPU time

[hombit]

I tried query method to ovoid row-by-row fetching, but execution time is almost the same.

I'm sorry that I can inform about a problem only, not give any solution, I'm very new in Rust.

use postgres::{Client, NoTls};

fn main() {
    let mut client = Client::connect("host=/var/run/postgresql user=user", NoTls).unwrap();
    let _rows = client.query("SELECT * FROM my_table", &[]).unwrap();
}

211.13s user 201.29s system 255% cpu 2:41.35 total

rust 1.34.0
postgres = "0.16.0-rc.1"

[sfackler]

That's expected. query just calls query_iter: https://github.com/sfackler/rust-postgres/blob/master/postgres/src/client.rs#L123

I'm working on a fix.

[sfackler]

#452 should improve things here, but I think there's some more follow-ups from that as well.

[hombit]

Wow! I tried #452 and you speeded it up from 200s to 8s!

6.72s user 3.50s system 121% cpu 8.388 total