Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i3hNM-0008Kn-PG for pgsql-sql@arkaria.postgresql.org; Fri, 30 Aug 2019 13:55:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i3hNL-0003Zd-L6 for pgsql-sql@arkaria.postgresql.org; Fri, 30 Aug 2019 13:55:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i3hNL-0003ZW-DA for pgsql-sql@lists.postgresql.org; Fri, 30 Aug 2019 13:55:39 +0000 Received: from mail150.strasbourg.4js.com ([77.159.205.150]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i3hNI-0008WD-Pe for pgsql-sql@lists.postgresql.org; Fri, 30 Aug 2019 13:55:38 +0000 Received: from [10.0.40.61] (toro.strasbourg.4js.com [10.0.40.61]) (authenticated bits=0) by mail150.strasbourg.4js.com (8.14.4/8.14.4/Debian-4+deb7u1) with ESMTP id x7UDtZXo031056 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NOT) for ; Fri, 30 Aug 2019 15:55:35 +0200 Subject: Re: libpq: How are result sets fetched behind the scene? To: pgsql-sql@lists.postgresql.org References: <6f2f210b-53e6-f350-4850-83f49b2a1354@4js.com> <7e0c3606-1513-1120-555a-6a14b615d939@4js.com> From: Sebastien FLAESCH Organization: Four Js Development Tools Message-ID: Date: Fri, 30 Aug 2019 15:55:35 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit X-Virus-Scanned: clamav-milter 0.99.4 at mail150 X-Virus-Status: Clean X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.6.2 (mail150.strasbourg.4js.com [10.10.0.1]); Fri, 30 Aug 2019 15:55:35 +0200 (CEST) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 >>> >>> >> >> >> > > >