Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bwuMS-0003Nv-AU for pgsql-performance@arkaria.postgresql.org; Wed, 19 Oct 2016 17:09:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bwuMR-0006St-QM for pgsql-performance@arkaria.postgresql.org; Wed, 19 Oct 2016 17:09:03 +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_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bwuKm-0004LE-V2 for pgsql-performance@postgresql.org; Wed, 19 Oct 2016 17:07:21 +0000 Received: from smtp-relay-00-3.dondominio.net ([31.214.176.16]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bwuKj-0007QZ-0P for pgsql-performance@postgresql.org; Wed, 19 Oct 2016 17:07:20 +0000 Received: from mail-node.dondominio.com by smtp-relay.dondominio.com (Postfix) with ESMTP id 2B3BD42DE7 for ; Wed, 19 Oct 2016 19:07:14 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=negora.com; s=dddk; t=1476896834; bh=lF+6BkgGN3ZohA40/Zm1jNZDqbaXjtk6ERSrsryui74=; h=Subject:To:From:Message-ID:Date:Content-Type; b=gaxNCIgkiQVc1Zbnin58ImeRjXJp+2ieYPKLkJQrsu8BoMJQwlgF1XJB4CiIzUuXZ wVwlOrV4nNr3Mi0pHhjp6k1o/6o7ReV4Y1Sr7/HAHBgxFC+kRecqgp/Of27weo4gJs Y8yc24j7kWm8Xy513f8TNrcwqWl4hXTB31yDfOSw= Received: by mail-node.dondominio.com (Postfix, from userid 4000) id 232A34179A; Wed, 19 Oct 2016 19:07:14 +0200 (CEST) Received: from [192.168.1.2] (177.red-88-14-80.dynamicip.rima-tde.net [88.14.80.177]) (Authenticated sender: public@negora.com) by mail-node.dondominio.com (Postfix) with ESMTPA id 9E27C41796 for ; Wed, 19 Oct 2016 19:07:13 +0200 (CEST) Subject: Re: Performance of a nested loop, whose inner loop uses an index scan. To: pgsql-performance@postgresql.org References: <5cb92dc3-af70-3cb1-5b2b-87ccc31f62cb@negora.com> From: negora X-Enigmail-Draft-Status: N1110 Message-ID: <6a9658d4-e878-2dfb-3dc6-4b358d8f16a2@negora.com> Date: Wed, 19 Oct 2016 19:07:13 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.2.6 (mail-node.dondominio.com [0.0.0.0]); Wed, 19 Oct 2016 19:07:13 +0200 (CEST) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org

Hi Matheus:

Thanks for your prompt answer. It's for a web application. This part of the application allows to export the answers to a CSV file. So pagination isn't possible here. The user can choose among several filters. The group of the courses is one of them. She can combine as many filters as she wants. So the query that I presented in my previous message was one of the "broadest" examples. But it's the one that I'm interested in.

Really, I'm more interested in the relative time than in the absolute time. Because I could create the file asynchronously, in the background, so that the user downloaded it at a later time. That's not the problem. My doubt is if 2.8 seconds is the best that I can do. Is it an acceptable time?

Thank you! ;)

On 19/10/16 13:15, Matheus de Oliveira wrote:

On Wed, Oct 19, 2016 at 8:54 AM, negora <public@negora.com> wrote:

Nested Loop (cost=245.92..383723.28 rows=7109606 width=38) (actual
time=1.091..2616.553 rows=8906075 loops=1)

I wonder about the use-case for this query, because it returns more than 8M rows, so 2.6 seconds that sounds that much for so many rows. Is it for an end user application? Isn't there any kind of pagination?

--

Matheus de Oliveira