Re: libpq: How are result sets fetched behind the scene? - Mailing list pgsql-sql

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