Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kTXxL-0005Yg-Il for pgsql-hackers@arkaria.postgresql.org; Fri, 16 Oct 2020 22:12:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kTXwl-0008Lp-Tr for pgsql-hackers@arkaria.postgresql.org; Fri, 16 Oct 2020 22:11:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kTXwl-0008Lg-JV for pgsql-hackers@lists.postgresql.org; Fri, 16 Oct 2020 22:11:35 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kTXwd-00036a-HF for pgsql-hackers@postgresql.org; Fri, 16 Oct 2020 22:11:34 +0000 Received: from [192.168.1.70] (109-252-139-43.dynamic.spd-mgts.ru [109.252.139.43]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id D001221C3AAB; Sat, 17 Oct 2020 01:11:25 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1602886285; bh=y0Dz1zjRi7GVkk5mL7ybNV6dLXGKnhv6JgzidGOtBoo=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=d3pUeEWO8yJckzPdOXGw8k39r143qC34t4uNUX3Zqt94lSK0QZy8kBDIeC5EzAg6E kQ5V9ilhWLsljV5GA0E5mEeBI9kSSxZ4QOOYHlFtsNKSNlKHeq7dRGqt1unZ+wQjtC CL6JPl9ac2R6DjC4PVtwf0vBdeHTVQv7gC2M2Q0Y= Subject: Re: [PATCH] Add extra statistics to explain for Nested Loop To: Julien Rouhaud , Pavel Stehule Cc: e.sokolova@postgrespro.ru, PostgreSQL Hackers References: From: Anastasia Lubennikova Message-ID: Date: Sat, 17 Oct 2020 01:11:24 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------0624DD0B5D4D29DB89AC2372" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------0624DD0B5D4D29DB89AC2372 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 16.10.2020 12:07, Julien Rouhaud wrote: > Le ven. 16 oct. 2020 à 16:12, Pavel Stehule > a écrit : > > > > pá 16. 10. 2020 v 9:43 odesílatel > napsal: > > Hi, hackers. > For some distributions of data in tables, different loops in > nested loop > joins can take different time and process different amounts of > entries. > It makes average statistics returned by explain analyze not > very useful > for DBA. > To fix it, here is the patch that add printing of min and max > statistics > for time and rows across all loops in Nested Loop to EXPLAIN > ANALYSE. > Please don't hesitate to share any thoughts on this topic! > > > +1 > > This is great feature - sometimes it can be pretty messy current > limited format > > > +1, this can be very handy! > Cool. I have added your patch to the commitfest, so it won't get lost. https://commitfest.postgresql.org/30/2765/ I will review the code next week.  Unfortunately, I cannot give any feedback about usability of this feature. User visible change is: -               ->  Nested Loop (actual rows=N loops=N) +              ->  Nested Loop (actual min_rows=0 rows=0 max_rows=0 loops=2) Pavel, Julien, could you please say if it looks good? -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------0624DD0B5D4D29DB89AC2372 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 16.10.2020 12:07, Julien Rouhaud wrote:
Le ven. 16 oct. 2020 à 16:12, Pavel Stehule <pavel.stehule@gmail.com> a écrit :


pá 16. 10. 2020 v 9:43 odesílatel <e.sokolova@postgrespro.ru> napsal:
Hi, hackers.
For some distributions of data in tables, different loops in nested loop
joins can take different time and process different amounts of entries.
It makes average statistics returned by explain analyze not very useful
for DBA.
To fix it, here is the patch that add printing of min and max statistics
for time and rows across all loops in Nested Loop to EXPLAIN ANALYSE.
Please don't hesitate to share any thoughts on this topic!

+1

This is great feature - sometimes it can be pretty messy current limited format

+1, this can be very handy! 

Cool.
I have added your patch to the commitfest, so it won't get lost.
https://commitfest.postgresql.org/30/2765/

I will review the code next week.  Unfortunately, I cannot give any feedback about usability of this feature.

User visible change is:

-               ->  Nested Loop (actual rows=N loops=N)
+              ->  Nested Loop (actual min_rows=0 rows=0 max_rows=0 loops=2)

Pavel, Julien, could you please say if it looks good?

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------0624DD0B5D4D29DB89AC2372--