Re: libpq: How are result sets fetched behind the scene? - Mailing list pgsql-sql
| From | Sebastien FLAESCH |
|---|---|
| Subject | Re: libpq: How are result sets fetched behind the scene? |
| Date | |
| Msg-id | d2e98168-fdc7-4ad8-d7c9-6213f730b8d7@4js.com Whole thread Raw |
| In response to | Re: libpq: How are result sets fetched behind the scene? (Sebastien FLAESCH <sf@4js.com>) |
| List | pgsql-sql |
By "slower" in the last sentence: It's slower because we execute the same query many times, it's not a single query execution. This is really about performances of server cursors when many DECLARE / CLOSE are done. Seb On 8/30/19 3:40 PM, Sebastien FLAESCH wrote: > Just tested (using valgrind --massif) how much memory is allocated on the client side. > > As I expected, when fetching all rows with simple query execution (no server cursor > using fetch forward to get rows in blocks), if you fetch all rows sequentially, a > lot of memory is allocated on the client side. > > This makes sense since PQgetvalue() purpose is to give access to any row / cols of > the whole result set. > > The valgrind --massif charts looks sometimes a bit strange to me: > > MB > 1.943^# > |#:::::::::::::: ::@:::::::@:::::: > |#:::::: :: : :: :: @:::::::@:::::: > |#:::::: :: : :: ::::: @:::::::@:::::: > |#:::::: :: : :: :::: :: @:::::::@:::::: > |#:::::: :: : :: :::::: :: @:::::::@:::::: > |#:::::: :: : :: ::::::::: :: @:::::::@:::::: > |#:::::: :: : :: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : :: :::::: :::::: :: @:::::::@:::::: > |#:::::: :: : :: ::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : :: ::::::::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : :: ::::@@::::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : :: :::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::::::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > |#:::::: :: : ::: ::::: :@ : ::: :: :::::: :::: :::::: :: @:::::::@:::::: > 0 +----------------------------------------------------------------------->Gi > 0 22.02 > > That one is more what I expect: > > MB > 1.943^## > |# :::::::::::::: :: : :@:::::@::::::::::::::::::::::::::::::::::::::: > |# :::: : :: :: : : : :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: : : : :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: :::::::@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > |# :::: : :: :: :@: : ::: :@: :: @:: :::: :: :: :: :::: :: :: ::: ::::::@ > 0 +----------------------------------------------------------------------->Gi > 0 22.10 > > Same query using server cursors, with fetch forward. > It's slower, but obviously less memory is used: > > > KB > 695.5^# : @@ > |#:::::::::::::::::@:@:::::@ @@:::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > |#:: ::::::::::::::@:@:::::@ @ :::::@::::::::@:::::@:::::@::::@:::::@:::: > 0 +----------------------------------------------------------------------->Gi > 0 22.37 > > > > > > Seb > > > > On 8/29/19 6:39 PM, Sebastien FLAESCH wrote: >> Sorry must read "fetched" in title. >> >> Similar question: >> >> When execution a SELECT (returning a large result set): >> >> Is the whole result set fetched to the client app, not matter what row number is provided to the first PQgetvalue() call(or similar API call on >> result set data or meta-data)? >> >> Seb >> >> On 8/29/19 6:32 PM, Sebastien FLAESCH wrote: >>> Hi all, >>> >>> We are using libpq in our C program. >>> >>> I was wondering how the client lib / protocol manage to fetch the result set to the client app memory. >>> >>> Is there some optimization ? >>> >>> Are rows fetched in sequence until the requested row? >>> >>> I mean if I do a SELECT that produces a huge result set, and then call PQgetvalue() with a row_number = 5000, are all5000 rows fetched from the >>> server or is there some smart fast-forward done because I did not ask the 4999 previous rows? >>> >>> We know about server cursors (declare + fetch forward), and we do already fetch blocks of rows with this. >>> >>> But server cursors are slow (and certainly use more server resources), compared to direct SELECT execution. >>> >>> So we are wondering what would be the impact in terms of resources on the client application. >>> >>> Is there any doc link or blog that would explain best practices with libpq result set programming? >>> >>> Thanks! >>> Seb >>> >>> >> >> >> > > >