Skip to content
  • Ruslan Zakirov's avatar
    add IsMerged boolean column to reflect id != EffectiveId · 58e115db
    Ruslan Zakirov authored
    Most of queries on Tickets has id == EffectiveId condition
    that is required to skip merged tickets. Usually percentage
    of merged tickets is very low, so this condition is true.
    However, DBs like Pg and Oracle estimate number of returned
    records very wrong. Each column is very distinct and statisticly
    speaking chance that one record has the same value in id and
    EffectiveId is low, but this is only true when correlation
    is not accounted and it's not accounted in mentioned DBs.
    
    We add IsMerged column with "boolean" type that reflects
    id != EffectiveId. It fixes estimations of DB optimizers.
    Query execution plans get better.
    
    Instead of adding the column we could use NULLs in EffectiveId
    for records where now id = EffectiveId, but this means ORed
    conditions in JOIN clause for rare queries. Tests show that
    such solution result in terrible perfomance degradation on
    mysql in these rare situations.
    58e115db