[Info-ingres] Why x100 rocks

[Martin Bowes]

Hi All,

If you haven't played with x100 tables in 11.1 then it's time you looked at it.

We gave up on this query after watching it for over 3 hours grinding away on standard ingres table structures...
SELECT primary_sub.eid, primary_sub.ins_index, primary_sub.primary_level,
  primary_sub.primary_diagnosis, secondary.level AS secondary_level,
  secondary.diag_icd10 AS secondary_diagnosis, hesin_oper.oper4 AS operation_code,
  hesin_oper.opdate AS operation_date
FROM
(
  SELECT hesin.eid, hesin.ins_index, primary.diag_icd10 AS primary_diagnosis,
    primary.level AS primary_level
  FROM hesin
  INNER JOIN hesin_diag AS primary
    on hesin.eid = primary.eid
    AND hesin.ins_index = primary.ins_index
  WHERE primary.level = 1
  AND primary.diag_icd10 =  'Z966'
) AS primary_sub
INNER JOIN hesin_diag AS secondary
  on primary_sub.eid = secondary.eid
  AND primary_sub.ins_index = secondary.ins_index
INNER JOIN hesin_oper
  on primary_sub.eid = hesin_oper.eid
  AND primary_sub.ins_index = hesin_oper.ins_index
WHERE secondary.level = 2
UNION
SELECT secondary_sub.eid, secondary_sub.ins_index, primary.level AS primary_level,
  primary.diag_icd10 AS primary_diagnosis, secondary_sub.secondary_level,
  secondary_sub.secondary_diagnosis, hesin_oper.oper4 AS operation_code,
  hesin_oper.opdate AS operation_date
FROM
(
  SELECT hesin.eid, hesin.ins_index, secondary.diag_icd10 AS secondary_diagnosis,
    secondary.level AS secondary_level
  FROM hesin INNER
  JOIN hesin_diag AS secondary
    on hesin.eid = secondary.eid
        AND hesin.ins_index = secondary.ins_index
  WHERE secondary.level = 2
  AND secondary.diag_icd10 =  'Z966'
) AS secondary_sub
INNER JOIN hesin_diag AS primary
  on secondary_sub.eid = primary.eid
  AND secondary_sub.ins_index = primary.ins_index
INNER JOIN hesin_oper
  on secondary_sub.eid = hesin_oper.eid
  AND secondary_sub.ins_index = hesin_oper.ins_index
WHERE primary.level = 1;

So after a fair deal of work we converted the tables in question (which are essentially static) to be x100 tables and reran the query ... 72seconds!

X100! Not just a Fuji camera, or a rust inhibitor.

Marty
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20190913/e151bcb9/attachment.html>