Thread: 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
Attachment
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
Attachment
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