Обсуждение: slight tweaks to documentation about runtime pruning

Hi,

Documentation of run-time pruning tells readers to inspect "nloops"
property of the EXPLAIN ANALYZE output, but I think that's a typo of
"loops" which is actually output ("internal variable to track that
property is indeed nloops).

However, for pruned partitions' subplans, what's actually shown is the
string "(never executed)", not loops.  So, wouldn't it be better to tell
the readers to look for that instead of "loops"?

Attached is what I have in mind.

(cc'ing David Rowley to get his opinion, as he presumably wrote that line.)

Thanks,
Amit

On 2018/12/05 16:23, Amit Langote wrote:
> Hi,
> 
> Documentation of run-time pruning tells readers to inspect "nloops"
> property of the EXPLAIN ANALYZE output, but I think that's a typo of
> "loops" which is actually output ("internal variable to track that
> property is indeed nloops).
> 
> However, for pruned partitions' subplans, what's actually shown is the
> string "(never executed)", not loops.  So, wouldn't it be better to tell
> the readers to look for that instead of "loops"?
> 
> Attached is what I have in mind.

Adding this to January CF.

Thanks,
Amit

On Wed, 5 Dec 2018 at 20:24, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Documentation of run-time pruning tells readers to inspect "nloops"
> property of the EXPLAIN ANALYZE output, but I think that's a typo of
> "loops" which is actually output ("internal variable to track that
> property is indeed nloops).

I agree. The 'n' should be dropped there.

> However, for pruned partitions' subplans, what's actually shown is the
> string "(never executed)", not loops.  So, wouldn't it be better to tell
> the readers to look for that instead of "loops"?

I don't really see any issues with the existing documentation here.
Remember that pruning can be performed multiple times when a parameter
changes that was found to match the partition key and the
Append/MergeAppend is rescanned. For example:

create table listp (a int) partition by list(a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2);
create index on listp(a);
set enable_bitmapscan=0;
explain analyze select * from (values(1),(1),(2)) a(a) inner join
listp l on a.a = l.a;
                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.15..91.50 rows=76 width=8) (actual
time=0.013..0.013 rows=0 loops=1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4)
(actual time=0.001..0.002 rows=3 loops=1)
   ->  Append  (cost=0.15..30.23 rows=26 width=4) (actual
time=0.003..0.003 rows=0 loops=3)
         ->  Index Only Scan using listp1_a_idx on listp1 l
(cost=0.15..15.05 rows=13 width=4) (actual time=0.002..0.002 rows=0
loops=2)
               Index Cond: (a = "*VALUES*".column1)
               Heap Fetches: 0
         ->  Index Only Scan using listp2_a_idx on listp2 l_1
(cost=0.15..15.05 rows=13 width=4) (actual time=0.003..0.003 rows=0
loops=1)
               Index Cond: (a = "*VALUES*".column1)
               Heap Fetches: 0
 Planning Time: 0.158 ms
 Execution Time: 0.042 ms
(11 rows)

listp1 was scanned twice (loops=2), listp2 was scanned just once.

Now it is true that if the subplan was executed 0 times that it will
appear as "(never executed)", but do we really need to explain in this
area that "(never executed)" means loops=0?

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Dec 9, 2018 at 8:13 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:
> listp1 was scanned twice (loops=2), listp2 was scanned just once.
>
> Now it is true that if the subplan was executed 0 times that it will
> appear as "(never executed)", but do we really need to explain in this
> area that "(never executed)" means loops=0?

Ah, I see what you mean.  So, "(never executed)" is not the only sign
of of run-time pruning occurring.  The value of loops can be different
for different subplans / partitions, and it being lower for a given
subplan means that the subplan has been pruned more number of times.

Thanks,
Amit

On 2018/12/10 0:57, Amit Langote wrote:
> On Sun, Dec 9, 2018 at 8:13 PM David Rowley
> <david.rowley@2ndquadrant.com> wrote:
>> listp1 was scanned twice (loops=2), listp2 was scanned just once.
>>
>> Now it is true that if the subplan was executed 0 times that it will
>> appear as "(never executed)", but do we really need to explain in this
>> area that "(never executed)" means loops=0?
> 
> Ah, I see what you mean.  So, "(never executed)" is not the only sign
> of of run-time pruning occurring.  The value of loops can be different
> for different subplans / partitions, and it being lower for a given
> subplan means that the subplan has been pruned more number of times.

I updated the patch.  Regarding whether we should mention "(never
executed)", it wouldn't hurt to mention it imho, exactly because it's
shown in the place of showing loops=0.  How about the attached?

Thanks,
Amit

On 2018-Dec-10, David Rowley wrote:

> On Wed, 5 Dec 2018 at 20:24, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:

> > However, for pruned partitions' subplans, what's actually shown is the
> > string "(never executed)", not loops.  So, wouldn't it be better to tell
> > the readers to look for that instead of "loops"?
> 
> I don't really see any issues with the existing documentation here.
> Remember that pruning can be performed multiple times when a parameter
> changes that was found to match the partition key and the
> Append/MergeAppend is rescanned.

I lean towards Amit's side.  I think we're too laconic about many
details of EXPLAIN's output.  This is two lines about an interesting
detail that's pretty obscure.  It doesn't hurt to have it there.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-Dec-14, Amit Langote wrote:

> I updated the patch.  Regarding whether we should mention "(never
> executed)", it wouldn't hurt to mention it imho, exactly because it's
> shown in the place of showing loops=0.  How about the attached?

Pushed, thanks.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Dec 17, 2018 at 11:49 PM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> On 2018-Dec-14, Amit Langote wrote:
>
> > I updated the patch.  Regarding whether we should mention "(never
> > executed)", it wouldn't hurt to mention it imho, exactly because it's
> > shown in the place of showing loops=0.  How about the attached?
>
> Pushed, thanks.

Thank you!

Regards,
Amit

On Tue, 18 Dec 2018 at 03:49, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Pushed, thanks.

I just noticed that this is still open on the CF app. Marking as committed...

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services