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
