Thread: verbose cost estimate
Jeff said: https://www.postgresql.org/message-id/CAMkU%3D1zBJNVo2DGYBgLJqpu8fyjCE_ys%2Bmsr6pOEoiwA7y5jrA%40mail.gmail.com |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. I guess the goal is something like EXPLAIN(COSTS, VERBOSE) -- or some new option? ..would show something like Seq Scan on public.sites (cost=0.00..2.90 rows=160 width=107) Total tosts: Seq page: 1.01 Random page: 1.23 CPU tuple: .05 CPU oper: .01 Startup cost: [...] It seems to me that's "just" a matter of redefining Cost and fixing everything that breaks: struct Cost { double seq, rand; double cpu_tuple, cpu_index_tuple, cpu_oper; double parallel_setup; // This is probably always in startup_cost and never in run_cost double parallel_tuple; // This is probably always in run_cost and never in startup_cost double disable; }; I'm perhaps 50% done with that - is there some agreement that's a desirable goal and a good way to do it ? To give an idea what I'm doing, there's a bunch of stuff like this: - if (path1->startup_cost < path2->startup_cost) + if (cost_asscalar(&path1->startup_cost) < cost_asscalar(&path2->startup_cost)) - qual_arg_cost += index_qual_cost.startup + index_qual_cost.per_tuple; + cost_add2(&qual_arg_cost, &index_qual_cost.startup, &index_qual_cost.per_tuple); - if (cost.per_tuple > 10 * cpu_operator_cost) + if (cost_isgt_scalar(&cost.per_tuple, 10 * cpu_operator_cost)) And a great deal of stuff like this: - run_cost += cpu_run_cost; + cost_add(&run_cost, &cpu_run_cost); /* tlist eval costs are paid per output row, not per tuple scanned */ - startup_cost += path->pathtarget->cost.startup; - run_cost += path->pathtarget->cost.per_tuple * path->rows; + cost_add(&startup_cost, &path->pathtarget->cost.startup); + cost_add_mul(&run_cost, &path->pathtarget->cost.per_tuple, path->rows); path->startup_cost = startup_cost; - path->total_cost = startup_cost + run_cost; + cost_set_sum2(&path->total_cost, &startup_cost, &run_cost); As I've written it, that's somewhat different from Jeff's suggestion, as all the entries in my struct are in units of cost. That seems easier due to (for example) per-tablespace IO costs. I'd rather know sooner than later if there's a better way. Justin
Justin Pryzby <pryzby@telsasoft.com> writes:
> Jeff said:
>> |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.
> It seems to me that's "just" a matter of redefining Cost and fixing everything that breaks:
> struct Cost {
> double seq, rand;
> double cpu_tuple, cpu_index_tuple, cpu_oper;
> double parallel_setup; // This is probably always in startup_cost and never in run_cost
> double parallel_tuple; // This is probably always in run_cost and never in startup_cost
> double disable;
> };
> I'm perhaps 50% done with that - is there some agreement that's a desirable
> goal and a good way to do it ?
No, I think this will get rejected out of hand. The implications for
the planner's speed and memory consumption seem quite unacceptable
for the size of the benefit. What you're showing above probably
doubles the size of most Paths, and the added cycles in hot-spots
like add_path seem pretty daunting.
We had earlier discussions about just breaking out the disable_cost,
and even that didn't look very promising as a practical matter :-(.
Nobody is willing to give up even small slowdowns in everyday
planning speed for corner-case needs like these.
One idea that would alleviate some of the problems is to keep the
net cost as-is, and just add a separate struct of broken-down
cost. Then, for example, add_path doesn't change at all. But
this makes the memory consumption angle even worse.
Like Jeff, I've occasionally wished for info like this. But not
often, and not hard enough that I think the cost of getting it
would be justified.
Something that might be useful when you do want this info is to
change one of the cost parameters by some small delta, rerun the
plan, and see how much the total cost changes; that gives you a
local slope of the sensitivity function. Repeat as needed for
other cost parameters. The tedious part is probably verifying
that the shape of the plan didn't change (else the cost comparison
isn't telling you what you want). Perhaps building a tool
to automate that idea would be worthwhile.
regards, tom lane
+1, adding that sort of structure to Cost would get rejected out of hand.
however, having a 'disabled' bit be part of the cost structure is something
that I would support. This has been discussed previously, but even adding
one bit to Cost doesn't have everyone's support. The purpose of a disabled
bit would be to distinguish plans that had no disable_cost added to them
from plans that did so that the planner can choose the minimum cost
non-disabled plan, if any such plan exists, or choose the minimum cost plan
otherwise. A disable count could be used, but even a bool would probably
suffice.
thank you,
/Jim F
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Jim Finnerty <jfinnert@amazon.com> writes:
> +1, adding that sort of structure to Cost would get rejected out of hand.
> however, having a 'disabled' bit be part of the cost structure is something
> that I would support. This has been discussed previously, but even adding
> one bit to Cost doesn't have everyone's support. The purpose of a disabled
> bit would be to distinguish plans that had no disable_cost added to them
> from plans that did so that the planner can choose the minimum cost
> non-disabled plan, if any such plan exists, or choose the minimum cost plan
> otherwise. A disable count could be used, but even a bool would probably
> suffice.
If we did go that route, I think a disable count would be the right thing.
It wouldn't take up any more space than a bool, probably, once you account
for padding overhead. And the code impact in hotspots like add_path would
be just about the same too. The advantage of a count is that, for
example, if you have enable_seqscan off then a path containing three
seqscans could be considered inferior to one with two; but if we have
only a bool then we can't tell the difference.
(Having said that, I'm still about -0.5 or so on the idea. But if
we do it, we should do a count.)
regards, tom lane
On Mon, Dec 9, 2019 at 11:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > If we did go that route, I think a disable count would be the right thing. > It wouldn't take up any more space than a bool, probably, once you account > for padding overhead. And the code impact in hotspots like add_path would > be just about the same too. The advantage of a count is that, for > example, if you have enable_seqscan off then a path containing three > seqscans could be considered inferior to one with two; but if we have > only a bool then we can't tell the difference. I'm not sure that I buy the idea that a disable count wouldn't take up any more space. A Boolean could even be represented as a flag inside of a bitmask, taking up just one bit. But even if you used a whole byte for it, in the long term, that's going to be cheaper; people around here are not blind to the value of filling in holes left by padding. I do agree that an integer would give us more accurate planning. The question in my mind is whether we care. It's not crazy to say that disabling is more for testing than real use, that it's best effort, and that once we give up on it, we give up completely -- which would make a bool sufficient. Now the contrary position that we want to be more accurate than that is not crazy either, and it's easy to see why that would be more convenient with a complex plan. But the real issue there, in my view, is that there's no way to disable certain kinds of plans for just part of a query. Nor is there any way to politely inform the planner that its idea of how many rows a certain scan or join will return is bollocks, and let it know the real number. There's just no way at all - except in limited cases, some unprincipled hacks - to give the planner that kind of guidance, suggestion, recommendation, urging, advice, clue, inkling, indicator, or, you know, whatever other word we could use to describe that sort of thing. So we're left with crude tools that affect the whole query. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, Dec 07, 2019 at 11:34:12AM -0500, Tom Lane wrote:
>Justin Pryzby <pryzby@telsasoft.com> writes:
>> Jeff said:
>>> |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.
>
>> It seems to me that's "just" a matter of redefining Cost and fixing everything that breaks:
>
>> struct Cost {
>> double seq, rand;
>> double cpu_tuple, cpu_index_tuple, cpu_oper;
>> double parallel_setup; // This is probably always in startup_cost and never in run_cost
>> double parallel_tuple; // This is probably always in run_cost and never in startup_cost
>> double disable;
>> };
>
>> I'm perhaps 50% done with that - is there some agreement that's a desirable
>> goal and a good way to do it ?
>
>No, I think this will get rejected out of hand. The implications for
>the planner's speed and memory consumption seem quite unacceptable
>for the size of the benefit. What you're showing above probably
>doubles the size of most Paths, and the added cycles in hot-spots
>like add_path seem pretty daunting.
>
Yeah, that's an issue. But I have to admit my main issue with this
proposal is that I have no idea how I'd interpret this Cost. I mean,
what do the fields express for different types of paths? How do they
contribute to the actual cost of that path?
What I regularly wish to know the parts of the cost for individual
paths: how much is the I/O (and maybe some extra bits about caching,
random and sequential I/O), cost of quals/functions, and so on. But this
info is inherently path-specific, it makes little sense to include that
into the regular Path struct. Perhaps a path-specific struct, referenced
from the path and built only with verbose explain would be fine?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, 9 Dec 2019 at 17:14, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
> On Sat, Dec 07, 2019 at 11:34:12AM -0500, Tom Lane wrote:
> >Justin Pryzby <pryzby@telsasoft.com> writes:
> >> Jeff said:
> >>> |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.
> >
> >> It seems to me that's "just" a matter of redefining Cost and fixing everything that breaks:
> >
> >> struct Cost {
> >> double seq, rand;
> >> double cpu_tuple, cpu_index_tuple, cpu_oper;
> >> double parallel_setup; // This is probably always in startup_cost and never in run_cost
> >> double parallel_tuple; // This is probably always in run_cost and never in startup_cost
> >> double disable;
> >> };
> >
> >> I'm perhaps 50% done with that - is there some agreement that's a desirable
> >> goal and a good way to do it ?
> >
> >No, I think this will get rejected out of hand. The implications for
> >the planner's speed and memory consumption seem quite unacceptable
> >for the size of the benefit. What you're showing above probably
> >doubles the size of most Paths, and the added cycles in hot-spots
> >like add_path seem pretty daunting.
> >
>
> Yeah, that's an issue. But I have to admit my main issue with this
> proposal is that I have no idea how I'd interpret this Cost. I mean,
> what do the fields express for different types of paths? How do they
> contribute to the actual cost of that path?
What I think users would be able to do with this info is understand
which parameter to tweak to raise the estimated cost of the node.
Everyone knows if you see a index scan is being used but is taking
longer than a sequential scan then you might try raising
random_page_cost. But I rarely see people tweaking the more "exotic"
parameters like operator_tuple_cost or index_tuple_cost and when they
do they aren't really sure what nodes they're affecting...
I remember planning to do a very similar thing back in the 8.3 era and
never getting around to it. You could imaging even storing these for
the overall plan in the logs and building a large matrix of actual
execution values versus these broken out individual costs. Then it
becomes a standard linear optimization problem to find the optimal
values for each parameter to minimize inaccurate plan estimates (and
to identify cases where there are outliers).
--
greg
Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> ... Perhaps a path-specific struct, referenced
> from the path and built only with verbose explain would be fine?
How would that work, given that the planner doesn't know whether its
output is going to get explained? With features like the plan cache
and auto_explain in mind, it's very hard to see how you avoid having
to save the information always.
regards, tom lane
On Mon, Dec 09, 2019 at 05:27:01PM -0500, Greg Stark wrote:
>On Mon, 9 Dec 2019 at 17:14, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>
>> On Sat, Dec 07, 2019 at 11:34:12AM -0500, Tom Lane wrote:
>> >Justin Pryzby <pryzby@telsasoft.com> writes:
>> >> Jeff said:
>> >>> |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.
>> >
>> >> It seems to me that's "just" a matter of redefining Cost and fixing everything that breaks:
>> >
>> >> struct Cost {
>> >> double seq, rand;
>> >> double cpu_tuple, cpu_index_tuple, cpu_oper;
>> >> double parallel_setup; // This is probably always in startup_cost and never in run_cost
>> >> double parallel_tuple; // This is probably always in run_cost and never in startup_cost
>> >> double disable;
>> >> };
>> >
>> >> I'm perhaps 50% done with that - is there some agreement that's a desirable
>> >> goal and a good way to do it ?
>> >
>> >No, I think this will get rejected out of hand. The implications for
>> >the planner's speed and memory consumption seem quite unacceptable
>> >for the size of the benefit. What you're showing above probably
>> >doubles the size of most Paths, and the added cycles in hot-spots
>> >like add_path seem pretty daunting.
>> >
>>
>> Yeah, that's an issue. But I have to admit my main issue with this
>> proposal is that I have no idea how I'd interpret this Cost. I mean,
>> what do the fields express for different types of paths? How do they
>> contribute to the actual cost of that path?
>
>What I think users would be able to do with this info is understand
>which parameter to tweak to raise the estimated cost of the node.
>
>Everyone knows if you see a index scan is being used but is taking
>longer than a sequential scan then you might try raising
>random_page_cost. But I rarely see people tweaking the more "exotic"
>parameters like operator_tuple_cost or index_tuple_cost and when they
>do they aren't really sure what nodes they're affecting...
>
Well, but that's kinda my point - how would you know that you need to
increase random_page_cost, or how big influence it has? The total is a
fairly non-trivial combination of various cost parameters, effective
cache size etc. Maybe I just don't understand how the cost is split into
those pieces, named the same as the cost GUCs ...
>I remember planning to do a very similar thing back in the 8.3 era and
>never getting around to it. You could imaging even storing these for
>the overall plan in the logs and building a large matrix of actual
>execution values versus these broken out individual costs. Then it
>becomes a standard linear optimization problem to find the optimal
>values for each parameter to minimize inaccurate plan estimates (and
>to identify cases where there are outliers).
>
Maybe, but that's for one query. If you do this for many queries, the
results may be easily contradicting, no?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Dec 09, 2019 at 05:40:40PM -0500, Tom Lane wrote: >Tomas Vondra <tomas.vondra@2ndquadrant.com> writes: >> ... Perhaps a path-specific struct, referenced >> from the path and built only with verbose explain would be fine? > >How would that work, given that the planner doesn't know whether its >output is going to get explained? With features like the plan cache >and auto_explain in mind, it's very hard to see how you avoid having >to save the information always. > I don't know, but my assumption is that this information would be needed only very rarely. So maybe we could pass a flag enabling this to the planner when executed from explain, and disable storing the plan in the plan cache, or something. And the additional info would be only available when explicitly requested using an extra EXPLAIN option. So for example auto_explain would not really show this (or it might get an extra option, with additional overhead). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Dec 10, 2019 at 12:25:46AM +0100, Tomas Vondra wrote: > >Everyone knows if you see a index scan is being used but is taking > >longer than a sequential scan then you might try raising > >random_page_cost. But I rarely see people tweaking the more "exotic" > >parameters like operator_tuple_cost or index_tuple_cost and when they > >do they aren't really sure what nodes they're affecting... > > > > Well, but that's kinda my point - how would you know that you need to > increase random_page_cost, or how big influence it has? The total is a > fairly non-trivial combination of various cost parameters, effective > cache size etc. Maybe I just don't understand how the cost is split into > those pieces, named the same as the cost GUCs ... Everything which right now does: |cost += something*random_page_cost ..ends up (via a macro): cost.random_page_cost += random_page_cost And everything which does: |cost1 += cost2 ..ends up doing the same for each of the component members. 99% of this falls into place trivially. I'm attaching a patch which is perhaps 95% working; various plans have changed, so I gather there's at least a few bugs. There's probably a few things which could be improved: Probably some "Costs" should be simple doubles if they're only ever multiples of a single cost parameter. Maybe someone will say that Cost should be a typedef to a struct* rather than a struct. Maybe I should get rid of cost_islt/isgt and just use cost_asscalar(). Seems like parallel_setup_cost and disable_cost could be booleans. Justin