Re: verbose cost estimate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: verbose cost estimate
Date: 2019-12-07 16:34:12
Message-ID: 27647.1575736452@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-12-07 17:16:17 Re: ssl passphrase callback
Previous Message Mark Dilger 2019-12-07 15:19:30 Re: psql small improvement patch