OuterJoinConversion performance/plan issue

[EPluribusUnum]

Hi *!

SELECT b.*
FROM ktbt bt
    LEFT OUTER JOIN fk_buf_tet b ON b.alrkod = 'KT' AND
          ((b.alrsor = bt.id) OR (b.alrsor = bt.st_id)) AND
          b.gysor = 1 AND
          b.gytip = 'A'
WHERE bt.id = 59747636 AND
      b.tabla = 'FK_TETEL'

This is fast with OuterJoinConversion = false, and slow with true.

I can send the database on request.

Thank you!

[omachtandras]

Converted like this it stays fast:
SELECT b.*
FROM ktbt bt
LEFT OUTER JOIN fk_buf_tet b ON b.alrkod = 'KT' AND
((b.alrsor = bt.id) OR (b.alrsor = bt.st_id)) AND
b.gysor = 1 AND
b.gytip = 'A' AND
b.tabla = 'FK_TETEL'
WHERE bt.id = 59747636 AND
b.id is not null

More information: the fk_buf_tet is a view, therefore, we do not use inner join, because in this case it losts index (primary key on ktbt) usage earlier.

[dyemanov]

More information: the fk_buf_tet is a view

And this is the root of the problem. We don't have a solution yet, still in progress (but doubtfully it will be available for pre-v6 versions -- too much risky).