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
Attachments:
runtime-pruning-doc-tweak.patchtext/plain; charset=UTF-8; name=runtime-pruning-doc-tweak.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 676a87aeb9..5730f359c8 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4395,8 +4395,9 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
query, partition pruning is performed whenever one of the
execution parameters being used by partition pruning changes.
Determining if partitions were pruned during this phase requires
- careful inspection of the <literal>nloops</literal> property in
- the <command>EXPLAIN ANALYZE</command> output.
+ careful inspection of the timing information in the
+ <command>EXPLAIN ANALYZE</command> output. Subplans corresponding
+ to pruned partitions are shown as <literal>(never executed)</literal>.
</para>
</listitem>
</itemizedlist>
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
Attachments:
runtime-pruning-doc-tweak-v2.patchtext/plain; charset=UTF-8; name=runtime-pruning-doc-tweak-v2.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1925ff4550..0d9353fc3b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4421,8 +4421,12 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
query, partition pruning is performed whenever one of the
execution parameters being used by partition pruning changes.
Determining if partitions were pruned during this phase requires
- careful inspection of the <literal>nloops</literal> property in
- the <command>EXPLAIN ANALYZE</command> output.
+ careful inspection of the <literal>loops</literal> property in
+ the <command>EXPLAIN ANALYZE</command> output. Subplans corresponding
+ to different partitions may have different values for it depending on
+ how many times each of them was pruned during execution. Some may be
+ shown as <literal>(never executed)</literal> if they were pruned every
+ time.
</para>
</listitem>
</itemizedlist>
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