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