Auto Complete Tuning

The speed of the autocomplete system is not good for the problem list terms. I have put all sorts of indexes and it is still slow the first time you search for a new concept. We are going to address that systematically, but without blind hacking. We need to try out certain GiST index options available for PostgreSQL.

drop table mw.wi_phrase
go
create table mw.wi_phrase (
  phrase text not null unique,
  count integer
) with oids
go
insert into mw.wi_phrase(phrase, count)
  select phrase, count(1)
    from concept_name_index
    group by phrase
go
drop table mw.wi_displayName
go
create table mw.wi_displayName (
  displayName text not null unique,
  count integer
) with oids
go
insert into mw.wi_displayName(displayName, count)
  select displayName, count(1)
    from concept_name_index
    group by displayName
go
drop table mw.wi_code
go
create table mw.wi_code (
  code text not null,
  codeSystem text not null,
  count integer,
  primary key(codeSystem, code)
) with oids
go
insert into mw.wi_code(codeSystem, code, count)
  select codeSystem, code, count(1)
    from concept_name_index
    group by codeSystem, code
go
drop table mw.wi_word
go
create table mw.wi_word (
  word text not null unique,
  count integer
) with oids
go
insert into mw.wi_word(word, count)
  select word, count(1)
    from concept_name_index
    group by word
go
drop table mw.wi_classAttr
go
create table mw.wi_classAttr (
  className text not null,
  attributeName text not null,
  count integer,
  primary key(className, attributeName)
) with oids
go
insert into mw.wi_classAttr(className, attributeName, count)
  select className, attributeName, count(1)
    from concept_name_index
    where className is not null
      and attributeName is not null
    group by className, attributeName
go
drop table mw.wi_obsCode
go
create table mw.wi_obsCode (
  observationCodeSystem text not null,
  observationCode text not null,
  count integer,
  primary key(observationCodeSystem, observationCode)
) with oids
go
insert into mw.wi_obsCode(observationCodeSystem, observationCode, count)
  select observationCodeSystem, observationCode, count(1)
    from mw.concept_name_index
    where observationCodeSystem is not null
      and observationCode is not null
    group by observationCodeSystem, observationCode
go
select oid, word from mw.wi_word limit 10
go
drop table mw.wi_hub
go
drop table mw.wi_hub
go
create table mw.wi_hub (
  code_id oid not null,
  displayName_id oid not null,
  word_id oid not null,
  wordSequenceNumber integer not null,
  phrase_id oid not null,
  classAtt_id oid not null,
  obsCode_id oid not null
)
go 
insert into mw.wi_hub(code_id, displayName_id, word_id, wordSequenceNumber, phrase_id, classAtt_id, obsCode_id)
  select c.oid, d.oid, w.oid, wordSequenceNumber, p.oid, ca.oid, oc.oid
    from mw.concept_name_index i
      inner join mw.wi_code c
        on(    c.codeSystem = i.codeSystem
           and c.code = i.code)
      inner join mw.wi_displayName d
        on(d.displayName = i.displayName)
      inner join mw.wi_word w on(w.word = i.word)
      inner join mw.wi_phrase p on(p.phrase = i.phrase)
      inner join mw.wi_classAttr ca
        on(    ca.className = i.className
           and ca.attributeName = i.attributeName)
      inner join mw.wi_obsCode oc
        on(    oc.observationCodeSystem = i.observationCodeSystem
           and oc.observationCode = i.observationCode)
go
drop table mw.wi_word_seq_code
go
create table mw.wi_word_seq_code (
  code_id oid not null,
  word_id oid not null,
  wordSequenceNumber integer not null,
  count integer not null,
  primary key(word_id, code_id, wordSequenceNumber)
)
go
insert into mw.wi_word_seq_code(code_id, word_id, wordSequenceNumber, count)
  select code_id, word_id, wordSequenceNumber, count(1)
    from mw.wi_hub
      group by code_id, word_id, wordsequencenumber
go
select * from wi_word_seq_code limit 5
go
explain analyze
select code_id
  from mw.wi_word_seq_code w1
    inner join mw.wi_word_seq_code w2 using(code_id)
    inner join mw.wi_word_seq_code w3 using(code_id)
  where w1.word_id in (select oid from mw.wi_word w where w.word like 'urinary')
    and w2.word_id in (select oid from mw.wi_word w where w.word like 'tract')
    and w3.word_id in (select oid from mw.wi_word w where w.word like 'infection')