Re: libpq: How are result sets fetched behind the scene?

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 all 5000 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
>>
>>
>
>
>