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 1i3h8g-0007TZ-6t for pgsql-sql@arkaria.postgresql.org; Fri, 30 Aug 2019 13:40:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i3h8f-0005Gi-21 for pgsql-sql@arkaria.postgresql.org; Fri, 30 Aug 2019 13:40:29 +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 1i3h8e-0005Bm-Of for pgsql-sql@lists.postgresql.org; Fri, 30 Aug 2019 13:40:28 +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 1i3h8c-0008IN-7i for pgsql-sql@lists.postgresql.org; Fri, 30 Aug 2019 13:40:28 +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 x7UDeOnl028446 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NOT) for ; Fri, 30 Aug 2019 15:40:24 +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:40:24 +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: <7e0c3606-1513-1120-555a-6a14b615d939@4js.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit 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:40:25 +0200 (CEST) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 >> >> > > >