Skip to content

An other OuterJoinConversion performance/plan issue #9129

Description

@EPluribusUnum

Hi *!

select ksp.id, rt.id, rt.gysor, rt.ktfj_id, coalesce(ksp.db_akt,0),
   rt.mert_id_keszlet, coalesce(rt.ef_egys_ar,0), coalesce(rt.ef_dev_egys_ar,0), rf.dev_kod,
   rt.kod1, rt.kod2, rt.kod3, rt.kod4, rt.kod5, rt.artip_kod,
   kne.nem_egalizalt, kne.mert_id, kne.m_mert_id, kne.sarzs_hasznal, kne.lejarat_hasznal, kne.id,
   ksp.sarzs, ksp.lejarat, ksp.gyartas, fj.ktfj_fafaj_id, rt.megj,
   szvitrt.kt_szvit_t_e_t_id
  from  ktrf rf
  join ktrt rt on rt.ktrf_id = rf.id and rt.tipus = 'T'
                and case when :kt_rend_lezar = 'T' then 1 else  coalesce(rt.vdb, rt.db) - coalesce(rt.ktdb, 0) end> 0 and rt.visszautasitva = 'N'
  join ktfj fj on fj.id = rt.ktfj_id
  left outer join kt_ktkf_adatok(rt.ktfj_id, :ktrk_kod, null) kne on 1=1
  left outer join ktrt_sarzsok_proc(rt.id,                                    case when :mtip = '2' then :ktmn__sarzs_megad else kne.sarzs_hasznal end,
                                    case when :mtip = '2' then :ktmn__lejarat_megad else case when kne.lejarat_hasznal = 'N' then 'N' else 'I' end end, 'I', 'K', 'N') ksp on 1=1
  left outer join kt_szvit_t_e_t_ktrt szvitrt on szvitrt.ktrt_id = rt.id
  where rf.id = :ktrf_id and
        fj.ktflag in ('K','R') and((kne.id is not null) or (fj.nincskarton = 'N'))  order by rt.gysor

/*
kt_rend_lezar = 'V'
ktrk_kod = 'A10'
mtip = '1'
ktmn__sarzs_megad = 'N'
ktmn__lejarat_megad = 'N'
ktrf_id = 32500779
*/

This is fast with OuterJoinConversion = false, and slow with true.
Tested with latest FB50 snapshot (build 1879)

I can send the database on request.

Thank you!

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions