[Info-ingres] A Really Slow Query
Michel Forget
mvf at cyberren.com
Thu Oct 18 22:55:09 UTC 2018
Hi,
I'm having a problem with the performance of a query and I'm hoping someone
can help.
The main problem is that this query takes literally minutes to run. Looking
at the QEP, things seem to go off the rails in the lower right-half of the
QEP. There is a huge chunk of CPU time on the right-join to the proj-rest
node that got data from secondary index alt2_d4. There is also then a huge
burst of disk time when it appears Ingres is pulling more data from base
table d4.
The trouble is I don't know what I can do to minimize these issues. I tried
rewriting the query to bypass the view completely, and that had a minimal
effect, but I feel like there is something fundamentally wrong with out
approach to indexes or how the query is written.
In any case, I thought I would appeal to smarter people than me. :)
I've tried to provide as much information as possible below (or at least
what I think is useful) but if I've missed something please let me know and
I'll supply it.
This particular set of information is from Ingres 10.x 64-bit (the free one)
but we've also tried it on an Ingres 9.x system (also free, also 64-bit).
All caches are enabled in cbf, and the guidelines are set to HUGE. All
remaining memory is file-system level caching. The server is a RHEL 7.3
64-Bit linux system. The table in question has ~2.5 million rows, which is
a ton.
1) The base table for the query is described below:
help table d4;
Executing . . .
Name: d4
Version: II10.0
Page size: 32768
Cache priority: 0
Alter table version: 0
Alter table totwidth: 4684
Row width: 4684
Number of rows: 2464056
Storage structure: isam with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 410781
Overflow data pages: 600
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog
Column Information:
Key Avg Count
Column Name Type Length Nulls Defaults
Seq Per Value
xorder_no integer 4 no no
1 unique
vrorderid varchar 15 yes null
xpid integer 4 no no
1047.9
xcauthor integer 4 no no
xcsource integer 4 yes null
11352.6
xparent integer 4 yes null
xlastcompletion integer 4 yes null
xplan_id integer 4 yes null
xfamily integer 4 yes null
drecord ingresdate no no
berror integer 1 no no
status integer 1 yes null
istate integer 4 yes null
strt_date ingresdate yes null
end_date ingresdate yes null
32.4
dend_save ingresdate yes null
norder_type integer 4 yes null
type integer 1 yes null
xstand integer 4 yes null
22.5
dbilling ingresdate yes null
ddeactivate ingresdate yes null
xcphysician_deactivate integer 4 yes null
16423.5
xcpersonnel_deactivate integer 4 yes null
xcprocedure integer 4 yes null
topic varchar 40 yes null
cmmcn char 1 yes null
prcs char 1 yes null
prcs_prsnl integer 4 yes null
prcs_time ingresdate yes null
nordr_crrd integer 2 yes null
crrd char 1 yes null
crrd_prsnl1 integer 4 yes null
8925.8
crrd_prsnl2 integer 4 yes null
crrd_time ingresdate yes null
2.8
verif char 1 yes null
410586.8
verif_prsnl integer 4 yes null
verif_time ingresdate yes null
cdeact_verif char 1 yes null
dverif_deact ingresdate yes null
cdeactack char 1 yes null
xdeactack integer 4 yes null
ddeactack ingresdate yes null
xcondition integer 4 yes null
xcondition2 integer 4 yes null
descr varchar 3000 yes null
nwmdose integer 2 yes null
nacdose integer 2 yes null
npcdose integer 2 yes null
nhsdose integer 2 yes null
namdose integer 2 yes null
npmdose integer 2 yes null
nprndose integer 2 yes null
npre_dialysis integer 2 yes null
nintra_dialysis integer 2 yes null
npost_dialysis integer 2 yes null
nperiod integer 2 yes null
nprd_typ integer 2 yes null
bwasted integer 2 yes null
bprinted integer 2 yes null
bsig integer 2 yes null
nappt integer 2 yes null
dhold ingresdate yes null
ccomments varchar 700 yes null
xdiscipline integer 4 yes null
dnext_date ingresdate yes null
btiming_pointer integer 2 yes null
btiming_units integer 2 yes null
burgency integer 2 yes null
bnonesrd integer 4 yes null
xsession integer 4 yes null
vnecomments varchar 500 yes null
vsigencrypt varchar 20 yes null
vsigencrypt2 varchar 20 yes null
bexportcreate integer 2 yes null
bexportdeact integer 2 yes null
Secondary indexes:
Index Name Structure Keyed On
alt10_d4 isam xcphysician_deactivate
alt11_d4 isam crrd_time
alt15_d4 btree xorder_no
alt1_d4 isam xpid
alt2_d4 btree verif
alt4_d4 isam xcsource
alt50_d4 btree xorder_no
alt5_d4 isam xstand
alt6_d4 isam crrd_prsnl1, crrd_time
alt7_d4 isam xstand
alt8_d4 isam end_date
2) The indexes for the table are described below:
create index alt10_d4 on d4
(xcphysician_deactivate,cdeact_verif,status,xFamily,bError)
with key=(xcphysician_deactivate), structure=isam;
create index alt11_d4 on d4 (crrd_time) with key = (crrd_time), structure =
isam;
create index alt15_d4 on d4 (xOrder_No, bUrgency, Crrd, dNext_Date) with
key=(xOrder_No), structure=btree;
create index alt1_d4 on d4
(xpid,drecord,topic,type,xorder_no,crrd,prcs,xcsource, berror,verif,
cdeact_verif, status, crrd_time, norder_type, xstand, strt_date,dnext_date,
dDeactivate) with key = (xpid), structure = isam;
create index alt2_d4 on d4 (verif, cdeact_verif, istate, cmmcn,
xcsource,xcPhysician_deactivate,xOrder_no) with key = (verif), structure =
btree;
create index alt4_d4 on d4
(xcsource,drecord,topic,type,xorder_no,crrd,prcs,xpid, berror,verif,status)
with key = (xcsource), structure = isam;
create index alt50_d4 on d4 (xOrder_No, bUrgency, Crrd, dNext_Date) with
key=(xOrder_No), structure=btree;
create index alt5_d4 on d4 (xstand,verif_prsnl,berror,crrd_time) with
key=(xstand), structure=isam;
create index alt6_d4 on d4 (crrd_prsnl1, crrd_time) with key=(crrd_prsnl1,
crrd_time), structure=isam;
create index alt7_d4 on d4 (xstand, type, crrd) with key = (xstand),
structure = isam;
create index alt8_d4 on d4 (end_date, xorder_no, berror, status) with key =
(end_date), structure = isam;
2) The query itself uses a view instead of a direct query agaisnt the table.
The view is described below:
create view d4_v99
(
xorder_no, bType, norder_type, dRecord, dStart,
dEnd,
iStatus, iState, vTopic, vDescr, vVerif,
Vcrrd,
vPrcs, xcsource, vAuthName, vCmmcn, xPlan,
xcdeact,
vDeactName, xStand, xFamily, cdeact_verif, dDeactivate,
hsp_pid, xpid, vname_given1, vname_given2,
vname_family, berror
)
as select
d4.xorder_no, /* 1 unique order number */
d4.type, /* 2 standing/specific */
d4.norder_type, /* 3 order type */
d4.drecord, /* 4 record date */
d4.strt_date, /* 5 order start date */
d4.end_date, /* 6 order and date */
d4.status, /* 7 order status */
ifnull(d4.iState, 99), /* 8 order state */
d4.topic, /* 9 order topic */
d4.Descr, /* 10 Order description */
ifnull(d4.verif,'N'), /* 11 verification status */
ifnull(d4.crrd, 'N'), /* 12 carried status */
ifnull(d4.prcs, 'N'), /* 13 acknowledged status */
d4.xcsource, /* 14 author */
c35_act.Full_Name, /* 15 acknowledging personal */
d4.cmmcn, /* 15b communication */
d4.xPlan_ID, /* 15c reference to Care Plan */
d4.xcphysician_deactivate, /* 16 physician deactivating */
c35_deact.full_name, /* 17 physician deactivating */
d4.xStand, /* 18 standing / specific */
d4.xFamily, /* 19 reference to the
parent order */
ifnull(d4.cdeact_verif, 'N'), /* 20 deactivation verified status */
d4.dDeactivate, /* 21 date deactivated */
d1.hsp_pid, /* 22 patient identifier */
d0.xpid, /* 23 internal patient index
*/
d0.name_given1, /* 24 first name */
d0.name_given2, /* 25 second name */
d0.name_family, /* 26 family name */
ifnull(d4.berror, 0) /* 27 discard status */
from d4
inner join d0 on d0.xpid = d4.xpid
inner join d1 on d1.xpid = d4.xpid
left join c35 c35_act on d4.xcsource = c35_act.xcsource
left join c35 c35_deact on d4.xcphysician_deactivate = c35_deact.xcsource;
4) The query, along with its QEP, is described below:
set qep;
set optimizeonly;
SELECT top 1000
d4_v99.dRecord,
d4_v99.dStart,
d4_v99.dEnd,
d4_v99.vTopic,
d4_v99.vVerif,
d4_v99.xOrder_no,
d4_v99.vCrrd,
d4_v99.vPrcs,
d4_v99.xcSource,
d4_v99.vAuthName,
d4_v99.xcDeact,
d4_v99.vDeactName,
d4_v99.bError,
d4_v99.bType,
d4_v99.iStatus,
d4_v99.cdeact_verif,
d4_v99.dDeactivate,
d4_v99.iState,
d4_v99.hsp_pid,
d4_v99.xPID,
d4_v99.vname_given1,
d4_v99.vname_given2,
d4_v99.vname_family
from d4_v99
WHERE (
(xcSource = 24093 AND vVerif != 'Y' )
or (xcDeact = 24093
AND cdeact_verif != 'Y'
and iStatus = 0) )
AND vCmmcn != 'A'
AND xPlan IS NULL
AND berror != 1
AND istate != 4
AND xOrder_No NOT IN
(SELECT xOrder from r59
where xSource = 24093) ;
Executing . . .
********************************************************************
QUERY PLAN 34,1, no timeout, of main query
Hash Join(xpid)
Heap
Pages 759 Tups 4549
D48778 C26835
/ \
Hash Join(xpid)
right join
Heap Hash
Join(xcphysician_deactivate)
Pages 12 Tups 2611 Heap
D29 C105
Pages 35 Tups 4549
D48749 C26613
/ \ /
\
Proj-rest Proj-rest Proj-rest
right join
Heap Sorted(NU) Heap
Hash Join(xcsource)
Pages 6 Tups 2615 Pages 35 Tups 2614 Pages 1 Tups 627
Heap
D16 C26 D13 C26 D2 C6
Pages 31 Tups 4549
D48747 C26549
/ / / /
\
d1 alt1_d0 c35
Proj-rest T Join(tidp)
Hashed(NU) I(d0) (c35_deact) Heap
Heap
Pages 130 Tups 2615 B-Tree(NU) Hashed(NU)
Pages 1 Tups 627 Pages 26 Tups 4549
Pages 51 Tups 2614 Pages 18 Tups 627 D2
C6 D48745 C26484
/
/ \
c35
right join d4
(c35_act)
Hash Join(xorder) Isam(NU)
Hashed(NU)
Partial(tidp) Pages 410751 Tups 2463873
Pages 18 Tups
627 Pages 396 Tups 45492
D5682 C25574
/
\
Proj-rest Proj-rest
Sorted(NU) Sorted(NU)
Pages 1 Tups 1 Pages 641 Tups 90983
D2
C0 D5513 C24640
/
/
alt1_r59
alt2_d4
I(r59)
I(d4)
B-Tree(NU)
B-Tree(NU)
Pages 9 Tups 1
Pages 22052 Tups 2464046
********************************************************************
E_OP0008 query optimized
- trace flag indicates no execution should take place
(Thu Oct 18 13:10:49 2018)
mvf
--
Michel Forget ( <mailto:mvf at cyberren.com> mvf at cyberren.com / Direct Line:
780-458-4989 x102)
Special Projects Analyst
Cybernius Medical, Ltd.
This e-mail may contain confidential and/or privileged information. This
information is intended only for the use of the individual(s) and
entity(ies) to whom it is addressed. If you are the intended recipient,
further disclosures are prohibited without proper authorization. If you are
not the intended recipient (or have received this e-mail in error) please
notify the sender immediately and destroy this e-mail. Any unauthorized
copying, disclosure or distribution of the material in this e-mail is
strictly forbidden and possibly a violation of federal or state law and
regulations. Cybernius Medical, its subsidiaries, and affiliates hereby
claim all applicable privileges related to this information.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20181018/42833aaf/attachment.html>
More information about the Info-ingres
mailing list