Re: verbose cost estimate

On Sat, Dec 07, 2019 at 11:34:12AM -0500, Tom Lane wrote:
>Justin Pryzby <pryzby(at)telsasoft(dot)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