On disable_cost

Hi,

Postgres has a global variable `disable_cost`. It is set the value
1.0e10.

This value will be added to the cost of path if related GUC is set off.
For example,
if enable_nestloop is set off, when planner trys to add nestloop join
path, it continues
to add such path but with a huge cost `disable_cost`.

But 1.0e10 may not be large enough. I encounter this issue in
Greenplum(based on postgres).
Heikki tolds me that someone also encountered the same issue on Postgres.
So I send it here to
have a discussion.

My issue: I did some spikes and tests on TPCDS 1TB Bytes data. For
query 104, it generates
nestloop join even with enable_nestloop set off. And the final plan's
total cost is very huge (about 1e24). But If I enlarge the disable_cost to
1e30, then, planner will generate hash join.

So I guess that disable_cost is not large enough for huge amount of
data.

It is tricky to set disable_cost a huge number. Can we come up with
better solution?

The following thoughts are from Heikki:

> Aside from not having a large enough disable cost, there's also the
> fact that the high cost might affect the rest of the plan, if we have to
> use a plan type that's disabled. For example, if a table doesn't have any
> indexes, but enable_seqscan is off, we might put the unavoidable Seq Scan
> on different side of a join than we we would with enable_seqscan=on,
> because of the high cost estimate.

> I think a more robust way to disable forbidden plan types would be to
> handle the disabling in add_path(). Instead of having a high disable cost
> on the Path itself, the comparison add_path() would always consider
> disabled paths as more expensive than others, regardless of the cost.

Any thoughts or ideas on the problem? Thanks!

Best Regards,
Zhenghua Lyu