Actual Cost
Hi,
When explaining a query, I think knowing the actual rows and pages in addition
to the operation type (e.g seqscan) would be enough to calculate the actual
cost. The actual cost metric could be useful when we want to look into how off
is the planner's estimation, and the correlation between time and cost. Would
it be a feature worth considering?
Thank you,
Donald Dong
On Sat, Feb 16, 2019 at 03:10:44PM -0800, Donald Dong wrote:
Hi,
When explaining a query, I think knowing the actual rows and pages
in addition to the operation type (e.g seqscan) would be enough to
calculate the actual cost. The actual cost metric could be useful
when we want to look into how off is the planner's estimation, and
the correlation between time and cost. Would it be a feature worth
considering?
As someone not volunteering to do any of the work, I think it'd be a
nice thing to have. How large an effort would you guess it would be
to build a proof of concept?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 2/17/19 3:40 AM, David Fetter wrote:
On Sat, Feb 16, 2019 at 03:10:44PM -0800, Donald Dong wrote:
Hi,
When explaining a query, I think knowing the actual rows and pages
in addition to the operation type (e.g seqscan) would be enough to
calculate the actual cost. The actual cost metric could be useful
when we want to look into how off is the planner's estimation, and
the correlation between time and cost. Would it be a feature worth
considering?As someone not volunteering to do any of the work, I think it'd be a
nice thing to have. How large an effort would you guess it would be
to build a proof of concept?
I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Feb 16, 2019, at 6:44 PM, Tomas Vondra wrote:
On 2/17/19 3:40 AM, David Fetter wrote:
As someone not volunteering to do any of the work, I think it'd be a
nice thing to have. How large an effort would you guess it would be
to build a proof of concept?I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.
Here is an example:
Hash Join (cost=3.92..18545.70 rows=34 width=32) (actual cost=3.92..18500 time=209.820..1168.831 rows=47 loops=3)
Now we have the actual time. Time can have a high variance (a change
in system load, or just noises), but I think the actual cost would be
less likely to change due to external factors.
On 2/17/19 3:40 AM, David Fetter wrote:
On Sat, Feb 16, 2019 at 03:10:44PM -0800, Donald Dong wrote:
Hi,
When explaining a query, I think knowing the actual rows and pages
in addition to the operation type (e.g seqscan) would be enough to
calculate the actual cost. The actual cost metric could be useful
when we want to look into how off is the planner's estimation, and
the correlation between time and cost. Would it be a feature worth
considering?As someone not volunteering to do any of the work, I think it'd be a
nice thing to have. How large an effort would you guess it would be
to build a proof of concept?
Intuitively it does not feel very complicated to me, but I think the
interface when we're planning (PlannerInfo, RelOptInfo) is different
from the interface when we're explaining a query (QueryDesc). Since
I'm very new, if I'm doing it myself, it would probably take many
iterations to get to the right point. But still, I'm happy to work on
a proof of concept if no one else wants to work on it.
regards,
Donald Dong
Donald Dong <xdong@csumb.edu> writes:
On Feb 16, 2019, at 6:44 PM, Tomas Vondra wrote:
I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.
Here is an example:
Hash Join (cost=3.92..18545.70 rows=34 width=32) (actual cost=3.92..18500 time=209.820..1168.831 rows=47 loops=3)
Now we have the actual time. Time can have a high variance (a change
in system load, or just noises), but I think the actual cost would be
less likely to change due to external factors.
I'm with Tomas: you have not explained what you think those
numbers mean.
regards, tom lane
On Feb 16, 2019, at 9:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Donald Dong <xdong@csumb.edu> writes:
On Feb 16, 2019, at 6:44 PM, Tomas Vondra wrote:
I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.Here is an example:
Hash Join (cost=3.92..18545.70 rows=34 width=32) (actual cost=3.92..18500 time=209.820..1168.831 rows=47 loops=3)
Now we have the actual time. Time can have a high variance (a change
in system load, or just noises), but I think the actual cost would be
less likely to change due to external factors.I'm with Tomas: you have not explained what you think those
numbers mean.
Yeah, I was considering the actual cost to be the output of the cost
model given the actual rows and pages after we instrument the
execution: plug in the values which are no longer estimations.
For a hash join, we could use the actual inner_rows_total to get the
actual cost. For a seqscan, we can use the actual rows to get the
actual CPU cost.
regards,
Donald Dong
On 2/17/19 7:45 AM, Donald Dong wrote:
On Feb 16, 2019, at 9:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Donald Dong <xdong@csumb.edu> writes:
On Feb 16, 2019, at 6:44 PM, Tomas Vondra wrote:
I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.Here is an example:
Hash Join (cost=3.92..18545.70 rows=34 width=32) (actual cost=3.92..18500 time=209.820..1168.831 rows=47 loops=3)
Now we have the actual time. Time can have a high variance (a change
in system load, or just noises), but I think the actual cost would be
less likely to change due to external factors.I'm with Tomas: you have not explained what you think those
numbers mean.Yeah, I was considering the actual cost to be the output of the cost
model given the actual rows and pages after we instrument the
execution: plug in the values which are no longer estimations.For a hash join, we could use the actual inner_rows_total to get the
actual cost. For a seqscan, we can use the actual rows to get the
actual CPU cost.
Perhaps I'm just too used to comparing the rows/pages directly, but I
don't quite see the benefit of having such "actual cost". Mostly because
the cost model is rather rough anyway.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sat, Feb 16, 2019 at 10:33 PM Donald Dong <xdong@csumb.edu> wrote:
On Feb 16, 2019, at 6:44 PM, Tomas Vondra wrote:
On 2/17/19 3:40 AM, David Fetter wrote:
As someone not volunteering to do any of the work, I think it'd be a
nice thing to have. How large an effort would you guess it would be
to build a proof of concept?I don't quite understand what is meant by "actual cost metric" and/or
how is that different from running EXPLAIN ANALYZE.Here is an example:
Hash Join (cost=3.92..18545.70 rows=34 width=32) (actual cost=3.92..18500
time=209.820..1168.831 rows=47 loops=3)Now we have the actual time. Time can have a high variance (a change
in system load, or just noises), but I think the actual cost would be
less likely to change due to external factors.
I don't think there is any way to assign an actual cost. For example how
do you know if a buffer read was "actually" seq_page_cost or
random_page_cost? And if there were a way, it too would have a high
variance.
What would I find very useful is a verbosity option to get the cost
estimates expressed as a multiplier of each *_cost parameter, rather than
just as a scalar. And at the whole-query level, get an rusage report
rather than just wall-clock duration. And if the HashAggregate node under
"explain analyze" would report memory and bucket stats; and if
the Aggregate node would report...anything.
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
What would I find very useful is a verbosity option to get the cost
estimates expressed as a multiplier of each *_cost parameter, rather than
just as a scalar.
Perhaps, but refactoring to get that seems impractically invasive &
expensive, since e.g. index AM cost estimate functions would have to
be redefined, plus we'd have to carry around some kind of cost vector
rather than single numbers for every Path ...
And at the whole-query level, get an rusage report
rather than just wall-clock duration.
I'm sure you're aware of log_statement_stats and friends already.
I agree though that that's not necessarily an optimal user interface.
regards, tom lane
On Feb 17, 2019, at 10:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Perhaps, but refactoring to get that seems impractically invasive &
expensive, since e.g. index AM cost estimate functions would have to
be redefined, plus we'd have to carry around some kind of cost vector
rather than single numbers for every Path ...
Maybe we could walk through the final plan tree and fill the expression? With another tree structure to hold the cost vectors.
On Feb 17, 2019, at 8:29 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
I don't think there is any way to assign an actual cost. For example how do you know if a buffer read was "actually" seq_page_cost or random_page_cost? And if there were a way, it too would have a high variance.
Thanks for pointing that out! I think it's probably fine to use the same assumptions as the cost model? For example, we assume 3/4ths of accesses are sequential, 1/4th are not.
What would I find very useful is a verbosity option to get the cost estimates expressed as a multiplier of each *_cost parameter, rather than just as a scalar.
Yeah, such expression would also help if we want to plug in the actual values.
On Feb 17, 2019, at 4:11 AM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
Perhaps I'm just too used to comparing the rows/pages directly, but I
don't quite see the benefit of having such "actual cost". Mostly because
the cost model is rather rough anyway.
Yeah, I think the "actual cost" is only "actual" for the cost model - the cost it would output given the exact row/page number. Some articles/papers have shown row estimation is the primary reason for planners to go off, so showing the actual (or the updated assumption) might also be useful if people want to compare different plans and want to refer to a more accurate quantitative measure.
regards,
Donald Dong
On Sun, Feb 17, 2019 at 11:29:56AM -0500, Jeff Janes wrote:
What would I find very useful is [...] an rusage report rather than just
wall-clock duration.
Most of that's available;
[pryzbyj@database ~]$ psql postgres -xtc "SET client_min_messages=log; SET log_statement_stats=on" -c 'SELECT max(i) FROM generate_series(1,999999)i'
SET
LOG: statement: SELECT max(i) FROM generate_series(1,999999)i
LOG: QUERY STATISTICS
DETAIL: ! system usage stats:
! 0.186643 s user, 0.033622 s system, 0.220780 s elapsed
! [0.187823 s user, 0.037162 s system total]
! 61580 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/7918 [0/9042] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/3 [2/3] voluntary/involuntary context switches
max | 999999
See also
https://commitfest.postgresql.org/20/1691/ => 88bdbd3f746049834ae3cc972e6e650586ec3c9d
/messages/by-id/7ffb9dbe-c76f-8ca3-12ee-7914ede872e6@stormcloud9.net
https://www.postgresql.org/docs/current/runtime-config-statistics.html#RUNTIME-CONFIG-STATISTICS-MONITOR
max RSS added in commit c039ba0716383ccaf88c9be1a7f0803a77823de1
Justin
On Feb 17, 2019, at 11:05 AM, Donald Dong <xdong@csumb.edu> wrote:
On Feb 17, 2019, at 10:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Perhaps, but refactoring to get that seems impractically invasive &
expensive, since e.g. index AM cost estimate functions would have to
be redefined, plus we'd have to carry around some kind of cost vector
rather than single numbers for every Path ...Maybe we could walk through the final plan tree and fill the expression? With another tree structure to hold the cost vectors.
Here is a draft patch. I added a new structure called CostInfo to the Plan node. The CostInfo is be added in create_plan, and the cost calculation is centered at CostInfo. Is this a reasonable approach?
Thank you,
Donald Dong
Attachments:
01_actual_cost_seqscan_001.patchapplication/octet-stream; name=01_actual_cost_seqscan_001.patch; x-unix-mode=0644Download
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 1831ea81cf..6a64282027 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -24,6 +24,7 @@
#include "nodes/extensible.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/cost.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
#include "storage/bufmgr.h"
@@ -1441,7 +1442,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
{
if (es->timing)
appendStringInfo(es->str,
- " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
+ " (actual cost=%.2f..%.2f time=%.3f..%.3f rows=%.0f loops=%.0f)",
+ plan->cost_info->startup_cost, actual_total_cost(plan, rows),
startup_ms, total_ms, rows, nloops);
else
appendStringInfo(es->str,
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 4b9be13f08..16c2c904bb 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -201,32 +201,23 @@ clamp_row_est(double nrows)
/*
- * cost_seqscan
- * Determines and returns the cost of scanning a relation sequentially.
- *
- * 'baserel' is the relation to be scanned
- * 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
+ * Fill the cost info structure. Later we use this structure to compute the
+ * cost estimation.
*/
-void
-cost_seqscan(Path *path, PlannerInfo *root,
+CostInfo *
+cost_info_seqscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info)
{
- Cost startup_cost = 0;
- Cost cpu_run_cost;
- Cost disk_run_cost;
- double spc_seq_page_cost;
+ Cost per_page_cost;
+ Cost per_row_cost;
+ Cost per_tuple_cost;
+ Cost startup_cost = 0.0;
+ double npages;
+ double ntuples;
+ double parallel_divisor;
QualCost qpqual_cost;
- Cost cpu_per_tuple;
+ CostInfo *cost_info;
- /* Should only be applied to base relations */
- Assert(baserel->relid > 0);
- Assert(baserel->rtekind == RTE_RELATION);
-
- /* Mark the path with the correct row estimate */
- if (param_info)
- path->rows = param_info->ppi_rows;
- else
- path->rows = baserel->rows;
if (!enable_seqscan)
startup_cost += disable_cost;
@@ -234,31 +225,93 @@ cost_seqscan(Path *path, PlannerInfo *root,
/* fetch estimated page cost for tablespace containing table */
get_tablespace_page_costs(baserel->reltablespace,
NULL,
- &spc_seq_page_cost);
+ &per_page_cost);
- /*
- * disk costs
- */
- disk_run_cost = spc_seq_page_cost * baserel->pages;
+ npages = baserel->pages;
/* CPU costs */
get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);
- startup_cost += qpqual_cost.startup;
- cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
- cpu_run_cost = cpu_per_tuple * baserel->tuples;
+ startup_cost = qpqual_cost.startup;
+ per_tuple_cost = cpu_tuple_cost + qpqual_cost.per_tuple;
+ ntuples = baserel->tuples;
/* tlist eval costs are paid per output row, not per tuple scanned */
startup_cost += path->pathtarget->cost.startup;
- cpu_run_cost += path->pathtarget->cost.per_tuple * path->rows;
+ per_row_cost = path->pathtarget->cost.per_tuple;
+ parallel_divisor = 1.0;
/* Adjust costing for parallelism, if used. */
if (path->parallel_workers > 0)
{
- double parallel_divisor = get_parallel_divisor(path);
+ parallel_divisor = get_parallel_divisor(path);
+ }
- /* The CPU cost is divided among all the workers. */
- cpu_run_cost /= parallel_divisor;
+ cost_info = palloc(sizeof(CostInfo));
+ cost_info->per_page_cost = per_page_cost;
+ cost_info->per_row_cost = per_row_cost;
+ cost_info->per_tuple_cost = per_tuple_cost;
+ cost_info->startup_cost = startup_cost;
+ cost_info->npages = npages;
+ cost_info->ntuples = ntuples;
+ cost_info->parallel_divisor= parallel_divisor;
+
+ return cost_info;
+}
+
+
+/*
+ * Calculate the total cost of a SeqScan
+ */
+double
+total_cost_seqscan(CostInfo *cost_info, double nrows)
+{
+ Cost disk_run_cost;
+ Cost cpu_run_cost;
+
+ /*
+ * disk costs
+ */
+ disk_run_cost = cost_info->per_page_cost * cost_info->npages;
+ cpu_run_cost = cost_info->per_tuple_cost * cost_info->ntuples + \
+ cost_info->per_row_cost * nrows;
+
+ /* The CPU cost is divided among all the workers. */
+ cpu_run_cost /= cost_info->parallel_divisor;
+
+ return cost_info->startup_cost + cpu_run_cost + disk_run_cost;
+}
+
+
+/*
+ * cost_seqscan
+ * Determines and returns the cost of scanning a relation sequentially.
+ *
+ * 'baserel' is the relation to be scanned
+ * 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
+ */
+void
+cost_seqscan(Path *path, PlannerInfo *root,
+ RelOptInfo *baserel, ParamPathInfo *param_info)
+{
+ double nrows;
+ CostInfo *cost_info;
+
+ /* Should only be applied to base relations */
+ Assert(baserel->relid > 0);
+ Assert(baserel->rtekind == RTE_RELATION);
+
+ /* Mark the path with the correct row estimate */
+ if (param_info)
+ nrows = param_info->ppi_rows;
+ else
+ nrows = baserel->rows;
+
+ cost_info = cost_info_seqscan(path, root, baserel, param_info);
+
+ /* Adjust costing for parallelism, if used. */
+ if (path->parallel_workers > 0)
+ {
/*
* It may be possible to amortize some of the I/O cost, but probably
* not very much, because most operating systems already do aggressive
@@ -270,11 +323,13 @@ cost_seqscan(Path *path, PlannerInfo *root,
* In the case of a parallel plan, the row count needs to represent
* the number of tuples processed per worker.
*/
- path->rows = clamp_row_est(path->rows / parallel_divisor);
+ path->rows = clamp_row_est(path->rows / cost_info->parallel_divisor);
}
- path->startup_cost = startup_cost;
- path->total_cost = startup_cost + cpu_run_cost + disk_run_cost;
+ path->startup_cost = cost_info->startup_cost;
+ path->total_cost = total_cost_seqscan(cost_info, nrows);
+
+ pfree(cost_info);
}
/*
@@ -5551,3 +5606,19 @@ compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel, Path *bitmapqual,
return pages_fetched;
}
+
+/*
+ * Get the actual total cost by using the actual number of rows
+ */
+double
+actual_total_cost(Plan *plan, double nrows)
+{
+ switch (nodeTag(plan))
+ {
+ case T_SeqScan:
+ return total_cost_seqscan(plan->cost_info, nrows);
+ default:
+ break;
+ }
+ return -1.0;
+}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 236f506cfb..3133eb79c0 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -349,6 +349,8 @@ create_plan(PlannerInfo *root, Path *best_path)
* re-used later
*/
root->plan_params = NIL;
+ plan->cost_info = cost_info_seqscan(best_path, root,
+ best_path->parent, best_path->param_info);
return plan;
}
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 6d087c268f..36534c619f 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -127,6 +127,8 @@ typedef struct Plan
double plan_rows; /* number of rows plan is expected to emit */
int plan_width; /* average row width in bytes */
+ struct CostInfo *cost_info;
+
/*
* information needed for parallel query
*/
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index ac6de0f6be..4576d3747d 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -38,6 +38,17 @@ typedef enum
CONSTRAINT_EXCLUSION_PARTITION /* apply c_e to otherrels only */
} ConstraintExclusionType;
+typedef struct CostInfo
+{
+ Cost per_page_cost;
+ Cost per_row_cost;
+ Cost per_tuple_cost;
+ Cost startup_cost;
+ double npages;
+ double ntuples;
+ double parallel_divisor;
+} CostInfo;
+
/*
* prototypes for costsize.c
@@ -199,4 +210,14 @@ extern PathTarget *set_pathtarget_cost_width(PlannerInfo *root, PathTarget *targ
extern double compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel,
Path *bitmapqual, int loop_count, Cost *cost, double *tuple);
+
+extern CostInfo *cost_info_seqscan(Path *path, PlannerInfo *root,
+ RelOptInfo *baserel, ParamPathInfo *param_info);
+
+extern double total_cost_seqscan(CostInfo *cost_info, double nrows);
+
+/* The actual total cost calculated based on the instument.
+ * Currently, only the number of rows is used to show a proof of concept.
+ */
+extern double actual_total_cost(Plan *plan, double nrows);
#endif /* COST_H */