Optimizer prunes efficient join orders when optimizer_prune_level=1

The customer complains about performance regression of a single query after upgrade from 10.5 to 10.6:

select

	distinct svc.idService,

	svc.deviceUID,

	svc.driverId,

	svc.lineId,

	svc.subLineId,

	svc.initDateTime,

	svc.endDateTime,

	svc.originStopId,

	svc.reinforcement,

	sd.hardwareId as vehicleIdentificationNumber

from

	Service svc

inner join ServiceData sd on

	svc.idService = sd.idService

	and svc.deviceUID = sd.deviceUID

inner join drivers d on

	d.code = svc.driverId

where

	((svc.initDateTime between '2025-02-11' and '2025-02-12')

		or (svc.endDateTime between '2025-02-11' and '2025-02-12'))

	and d.company_id in (51)

Database schema and optimizer traces for the query are attached.

10.5 employs join order ["d", "sd", "svc"] while 10.6 chooses another one: ["sd", "svc", "d"], and the latter proves to be suboptimal. A possible cause of this is the change of default value for `optimizer_prune_level` switch from `0` in 10.5 to `1` in 10.6. Running the query with optimizer_prune_level=0 in 10.6 provides the efficient join order ["d", "sd", "svc"] and time of execution comparable to the one in 10.5.

Looking at the optimizer trace of the slow execution in 10.6 (with optimizer_prune_level=1), we see:

---  10.6 -----

Table "sd"

  rows: 2383155

  cost: 6956

Table "svc"

  rows: 2383168

  cost: 488718.6

Table "d"

  rows: 408

  cost: 81

At this point the optimizer makes a decision to prune all join orders starting with tables "sd" and "svc" because "d" provides better cost and cardinality.

However, looking at the trace of fast execution in 10.5 (with optimizer_prune_level=1), we can find join orders starting with "d" are much more expensive than those starting with "sd" and "svc":

----  10.5 -----

join order "svc","sd","d"

  cost: 231'268  (minimal)

join order "svc","d","sd"

  cost: 453'897

join order "d","svc","sd"

  cost: 1'837'988

partial join order "d","sd" 

  cost: 194'472'420   (pruned by cost)

partial join order "sd"

  cost: 483'522   (pruned by cost)