First patch -- somewhat trivial feature
This is my first attempt at writing a patch, so it's pretty simple.
Commit log sums it up:
Adds a convenience feature to the explain command which prints out GUC cost
parameters in explain text output.
For example:
explain (params) select * from table;
will include text output like the following:
Cost Params:
seq_page: 1.000000, rnd_page: 4.000000, cpu_tup: 0.010000,
cpu_ind: 0.005000, cpu_op: 0.002500
amenabled: 11111111111
The bit vector is enable variables in the order listed in cost.h,
though mainly provides a high level view on whether or not any strategies
are disabled.
------
I recognize that this is kind of a frivolous feature which may not be worth
any potential maintenance burden, so submitted for what it's worth as an
initial effort.
Best Regards,
Robert
Attachments:
0001-Adds-a-convenience-feature-to-the-explain-command-wh.patchtext/x-patch; charset=US-ASCII; name=0001-Adds-a-convenience-feature-to-the-explain-command-wh.patchDownload
From 960dc64864306b554a2e150cb8e28e7a63f218d1 Mon Sep 17 00:00:00 2001
From: robert berry <berrydigital@gmail.com>
Date: Wed, 13 Nov 2013 15:29:23 -0800
Subject: [PATCH 1/1] Adds a convenience feature to the explain command which
prints out GUC cost parameters in explain text output.
For example:
will include text output like the following:
Cost Params:
seq_page: 1.000000, rnd_page: 4.000000, cpu_tup: 0.010000, cpu_ind: 0.005000, cpu_op: 0.002500
amenabled: 11111111111
The bit vector is enable variables in the order listed in cost.h, though mainly provides a high level
view on whether or not any strategies are disabled.
---
src/backend/commands/explain.c | 18 ++++++++++++++++++
src/include/commands/explain.h | 1 +
2 files changed, 19 insertions(+)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 4e93df2..8feeca8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -31,6 +31,7 @@
#include "utils/snapmgr.h"
#include "utils/tuplesort.h"
#include "utils/xml.h"
+#include "optimizer/cost.h"
/* Hook for plugins to get control in ExplainOneQuery() */
@@ -140,6 +141,8 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString,
es.costs = defGetBoolean(opt);
else if (strcmp(opt->defname, "buffers") == 0)
es.buffers = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "params") == 0)
+ es.params = defGetBoolean(opt);
else if (strcmp(opt->defname, "timing") == 0)
{
timing_set = true;
@@ -516,6 +519,21 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
PopActiveSnapshot();
+ /* include one line display of cost parameters */
+ if (es->params)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "\nCost Params:\n");
+ appendStringInfo(es->str, "\tseq_page: %f, rnd_page: %f, cpu_tup: %f, cpu_ind: %f, cpu_op: %f",
+ seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, enable_seqscan);
+ appendStringInfo(es->str, "\n\tamenabled: %d%d%d%d%d%d%d%d%d%d%d\n\n",
+ enable_seqscan, enable_indexscan, enable_indexonlyscan, enable_bitmapscan,
+ enable_tidscan, enable_sort, enable_hashagg, enable_nestloop, enable_material,
+ enable_mergejoin, enable_hashjoin);
+ }
+ }
+
/* We need a CCI just in case query expanded to multiple plans */
if (es->analyze)
CommandCounterIncrement();
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index ca213d7..d76476c 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -33,6 +33,7 @@ typedef struct ExplainState
bool costs; /* print costs */
bool buffers; /* print buffer usage */
bool timing; /* print timing */
+ bool params; /* print optimizer cost params */
ExplainFormat format; /* output format */
/* other states */
PlannedStmt *pstmt; /* top of plan */
--
1.8.3.2
* Robert Berry (berrydigital@gmail.com) wrote:
This is my first attempt at writing a patch, so it's pretty simple.
Neat!
seq_page: 1.000000, rnd_page: 4.000000, cpu_tup: 0.010000,
cpu_ind: 0.005000, cpu_op: 0.002500
amenabled: 11111111111The bit vector is enable variables in the order listed in cost.h,
though mainly provides a high level view on whether or not any strategies
are disabled.
While I like the general idea, I have to admit that I don't particularly
like the format and I'm not sure why it makes sense to have this as part
of 'explain'? Why not do a 'show all;' ahead of the explain?
I recognize that this is kind of a frivolous feature which may not be worth
any potential maintenance burden, so submitted for what it's worth as an
initial effort.
A bit more understanding about why you find it particularly useful (your
specific use-case for it) would be helpful. Also, may I suggest that
you look at http://commitfest.postgresql.org as you'll be asked to post
any patches you wish to submit there for review and consideration.
Thanks!
Stephen
Stephen Frost wrote
* Robert Berry (
berrydigital@
) wrote:
This is my first attempt at writing a patch, so it's pretty simple.
Neat!
seq_page: 1.000000, rnd_page: 4.000000, cpu_tup: 0.010000,
cpu_ind: 0.005000, cpu_op: 0.002500
amenabled: 11111111111The bit vector is enable variables in the order listed in cost.h,
though mainly provides a high level view on whether or not any strategies
are disabled.While I like the general idea, I have to admit that I don't particularly
like the format and I'm not sure why it makes sense to have this as part
of 'explain'? Why not do a 'show all;' ahead of the explain?
I kinda get the theory behind this but, WRT formatting, explain can output
multiple formats and any patch affecting said output should provide for
changing all of them. Having each of the sample outputs in the post would
allow for comments from those who would not generally apply such patches.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/First-patch-somewhat-trivial-feature-tp5778245p5778250.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Snowman --
Thanks for feedback. I imagined this feature could be useful in a couple
of contexts, though mainly in the sense of documenting query optimization
efforts.
In one case you may be tweaking cost parameters and having a built in
record of the parameters in the explain output can make that more reliable.
In a support context, it can help communicate all the details behind the
selection of a query plan, perhaps on IRC support.
David J. --
I originally conceived the notion for the feature while off in the deep end
thinking about automatic cost parameter selection.
It seems plausible that the ideal theoretical costs would be influenced by
server activity (io/cpu utilization) and various buffer states at a point
in time. This kind of optimization work is a little beyond my
understanding and capability so this was a first step in thinking about
this topic. It turned into a trivial user text output feature, but
implementing output formats that are more machine friendly still makes a
lot of sense.
Best Regards,
-Robert
On Wed, Nov 13, 2013 at 4:16 PM, David Johnston <polobo@yahoo.com> wrote:
Show quoted text
Stephen Frost wrote
* Robert Berry (
berrydigital@
) wrote:
This is my first attempt at writing a patch, so it's pretty simple.
Neat!
seq_page: 1.000000, rnd_page: 4.000000, cpu_tup: 0.010000,
cpu_ind: 0.005000, cpu_op: 0.002500
amenabled: 11111111111The bit vector is enable variables in the order listed in cost.h,
though mainly provides a high level view on whether or not anystrategies
are disabled.
While I like the general idea, I have to admit that I don't particularly
like the format and I'm not sure why it makes sense to have this as part
of 'explain'? Why not do a 'show all;' ahead of the explain?I kinda get the theory behind this but, WRT formatting, explain can output
multiple formats and any patch affecting said output should provide for
changing all of them. Having each of the sample outputs in the post would
allow for comments from those who would not generally apply such patches.David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/First-patch-somewhat-trivial-feature-tp5778245p5778250.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers