Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.7.12
    • Fix Version/s: 1.7.12
    • Component/s: None
    • Labels:
      None
    • Rank:
      0|s0008w:
    • Affects Database/s:
      PostgreSQL

      Description

      The postgres totalnum script takes a very long time to run because a self-join on an ontology table with a LIKE clause on c_fullname takes ages. The same query on MSSQL with the same tables and same indices takes <5s.

      select distinct p1.c_fullname, p2.c_basecode
      from dimCountOnt p1
      inner join dimCountOnt p2
      on p2.c_fullname like p1.c_fullname||'%' escape '#'
       where p2.c_fullname like '\i2b2\Diagnoses\%' escape '#'
      and p1.c_fullname like '\\i2b2\\Diagnoses\\%'

      Using specialized postgres indices did not help:

      create index dimCountOntA on dimCountOnt using spgist (c_fullname);
      CREATE INDEX dimCountOntB ON dimCountOnt(c_fullname text_pattern_ops);
      CREATE INDEX dimCountOntBjk ON dimCountOnt USING btree(c_fullname varchar_pattern_ops);

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code