Re: On disable_cost

On Tue, Apr 2, 2024 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I'm pretty sure negative costs are going to create a variety of
> > unpleasant planning artifacts.
>
> Indeed.  It might be okay to have negative values for disabled-ness
> if we treat disabled-ness as a "separate order of infinity", but
> I suspect that it'd behave poorly when there are both disabled and
> promoted sub-paths in a tree, for pretty much the same reasons you
> explained just upthread.

Hmm, can you explain further? I think essentially you'd be maximizing
#(promoted notes)-#(disabled nodes), but I have no real idea whether
that behavior will be exactly what people want or extremely
unintuitive or something in the middle. It seems like it should be
fine if there's only promoting or only disabling or if we can respect
both the promoting and the disabling, assuming we even want to have
both, but I'm suspicious that it will be weird somehow in other cases.
I can't say exactly in what way, though. Do you have more insight?

> > I think the only reason we're
> > driving this off of costing today is that making add_path() more
> > complicated is unappealing, mostly on performance grounds, and if you
> > add disabled-ness (or promoted-ness) as a separate axis of value then
> > add_path() has to know about that on top of everything else.
>
> It doesn't seem to me that it's a separate axis of value, just a
> higher-order component of the cost metric.  Nonetheless, adding even
> a few instructions to add_path comparisons sounds expensive.  Maybe
> it'd be fine, but we'd need to do some performance testing.

Hmm, yeah. I'm not sure how much difference there is between these
things in practice. I didn't run down everything that was happening,
but I think what I did was equivalent to making it a higher-order
component of the cost metric, and it seemed like an awful lot of paths
were surviving anyway, e.g. index scans survived
enable_indexscan=false because they had a sort order, and I think
sequential scans were surviving enable_seqscan=false too, perhaps
because they had no startup cost. At any rate there's no question that
add_path() is hot.

--
Robert Haas
EDB: http://www.enterprisedb.com