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!

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