From: | Sebastien FLAESCH <sf(at)4js(dot)com> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: libpq: How are result sets fetched behind the scene? |
Date: | 2019-08-30 13:55:35 |
Message-ID: | d2e98168-fdc7-4ad8-d7c9-6213f730b8d7@4js.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
>>>
>>>
>>
>>
>>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Martin | 2019-09-05 11:04:39 | Question about WHERE CASE |
Previous Message | Sebastien FLAESCH | 2019-08-30 13:40:24 | Re: libpq: How are result sets fetched behind the scene? |