|
I have asked specific questions and paid attention to the various threads on configuration. I will take my config files and post on the performance thread that is a good suggestion (personnaly I have more faith in this forum then a paid consultant, but at this point I am willing to try both).
Thanks again.
The count thing I can get around using stored results and on demand counting, but some of my statistical reporting is just a must have. I enclosed one of my views, I realize to get help I should also include tables and indexes etc, and maybe I will do that. It is just there are so many of them. This one in particular did not run at all when I first got my data loaded. I ended up adding a few indexes and not sure what else and got it to run faster on postgres. Now it is running horrid, so I am back to the drawing board I change one thing and something else breaks. I am just frustrated, maybe Monday I will have better strength to figure it all out.
Joel Fradkin
|
CREATE OR REPLACE VIEW viwcasecube
as
/*customer 1*/
select c.clientnum,c.casenum,c.casereferencenum, coalesce
((select sum(quantity * amount)
as merchandiseamount
from tblmerchandise m
where m.caseid = c.caseid and m.clientnum=c.clientnum), 0)
|| coalesce(c.totaladmitted, 0) as casevalue,
coalesce(c.totaladmitted, 0) as admitted,
coalesce(c.totalrecovery, 0) as recovered,
coalesce(c.ageatcase, 0) as ageatcase,
1 as numberofcase, coalesce(ct.value,'na') as casetype,
s.value as status, c.opendate, c.closedate,
a.value as action, u2.completename as closebyuser,
cs.value as casesource, m.value as method, m2.value as method2,
c.reportingagentfirstinitial, c.reportingagentlastname,
case when c.monthsemployedatcase is null then 'na'
else cast(c.monthsemployedatcase as varchar(3))
end as monthsemployedatcase,
u1.completename as createdby, st.value as subjecttype,
ot.value as offensetype,
/*cust*/ custpt.value as patrontype,
'na' as jobtitle,
0 as testscore,
coalesce(cust.firstname,'na') as firstname,
coalesce(cust.lastname,'na') as lastname,
coalesce(cust.address,'na') as address,
coalesce(cust.city,'na') as city,
coalesce(cust.state,'na') as state,
coalesce(cust.zip,'na') as zip,
coalesce(crtt.value,'na') as restitutiontype,
/*
type of restitution tracking
*/
coalesce(tblsex.value,'na') as gender,
coalesce(eth.value,'na') as ethnicity,
custmbt.value as militarybranch,
custmst.value as militarystatus,
coalesce(secagentnum,'not recorded') as secagentnum,
l.locationnum, l.name as store,
coalesce(l.address,'na') as locationaddress,
coalesce(l.city,'na') as locationcity,
coalesce(l.state,'na') as locationstate,
coalesce(l.zip,'na') as locationzip,
d .districtnum,
d .districtname as district, r.regionnum,
r.regionname as region, dv.divisionnum,
dv.divisionname as division,
case when c.apprehdate is null then c.opendate
else c.apprehdate
end as apprehdate,
to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year,
to_char( coalesce(c.apprehdate, c.opendate),'q') as
quarter,
to_char( coalesce(c.apprehdate, c.opendate),'MM') as
month,
to_char( coalesce(c.apprehdate, c.opendate),'D') as
weekday,
to_char( coalesce(c.apprehdate, c.opendate),'WW') as week,
to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as
time,
coalesce(c.sourcedocnum,'none') as sourcedocnum,
case coalesce(c.sourcemodule,'n')
when 'n' then 'none'
when 'i' then 'incident'
when 'g' then 'general investigation'
when 'e' then 'employee investigation'
else 'none'
end as sourcemodule,
case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber,
'no')
when 'nono' then 'no' else 'yes' end as civilcase,
coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na')
as lpmanager
from tblcase c left outer join
tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1=
a.presentationid left outer join
tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1=
m.presentationid left outer join
tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1=
m2.presentationid left outer join
tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum
and 1= cs.presentationid
inner join
tbllocation l
left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and
l.clientnum = lpr.clientnum and 1= lpr.presentationid
on c.clientnum = l.clientnum and
c.locationid = l.locationid
inner join
tbldistrict d on
c.clientnum = d.clientnum and
l.districtid = d.districtid and
l.regionid = d.regionid and
l.divisionid = d.divisionid
inner join
tblregion r on
c.clientnum = r.clientnum and
l.regionid = r.regionid and
l.divisionid = r.divisionid
inner join
tbldivision dv on
c.clientnum = dv.clientnum and
l.divisionid = dv.divisionid
left outer join
tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum =
crtt.clientnum and 1= crtt.presentationid left outer join
tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join
tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left
outer join
tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and
1= ct.presentationid left outer join
tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and
1= u1.presentationid left outer join
tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1=
u2.presentationid left outer join
tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum =
ot.clientnum and 1= ot.presentationid left outer join
tblcustomer cust
on c.subjectid = cust.customerid and c.clientnum = cust.clientnum
left outer join tblethnicity eth on cust.ethnicityid = eth.id and
cust.clientnum = eth.clientnum and 1= eth.presentationid
left outer join tblsex on cust.sexid = tblsex.id and 1=
tblsex.presentationid
left outer join tblmilitarybranch custmbt on cust.militarybranchid =
custmbt.id and cust.clientnum = custmbt.clientnum and 1= custmbt.presentationid
left outer join tblmilitarystatus custmst on cust.militarystatusid
= custmst.id and cust.clientnum = custmst.clientnum and 1=
custmst.presentationid
left outer join tblpatrontype custpt on cust.patrontypeid = custpt.id
and cust.clientnum = custpt.clientnum and 1= custpt.presentationid
left join
tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and
c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum
left join tblcdacases cdacase
on c.clientnum = cdacase.clientnum and c.casenum =
cdacase.clicasenumber
where c.isdeleted = false and c.subjecttypeid = 1/* and c.clientnum =
'waz'*/
union /*assoc 6*/
select c.clientnum,c.casenum, c.casereferencenum,coalesce
((select sum(quantity * amount)
as merchandiseamount
from tblmerchandise m
where m.caseid = c.caseid and m.clientnum=c.clientnum), 0)
|| coalesce(c.totaladmitted, 0) as casevalue,
coalesce(c.totaladmitted, 0) as admitted,
coalesce(c.totalrecovery, 0) as recovered,
coalesce(c.ageatcase, 0) as ageatcase,
1 as numberofcase, coalesce(ct.value,'na') as casetype,
s.value as status, c.opendate, c.closedate,
a.value as action, u2.completename as closebyuser,
cs.value as casesource, m.value as method, m2.value as method2,
c.reportingagentfirstinitial, c.reportingagentlastname,
case when c.monthsemployedatcase is null then 'na'
else cast(c.monthsemployedatcase as varchar(3))
end as monthsemployedatcase,
u1.completename as createdby, st.value as subjecttype,
ot.value as offensetype,
pt.value as patrontype,
jt.value as jobtitle,
coalesce(ac.testscore,0) as testscore,
coalesce(ac.firstname,'na') as firstname,
coalesce(ac.lastname,'na') as lastname,
coalesce(ac.address,'na') as address,
coalesce(ac.city,'na') as city,
coalesce(ac.state,'na') as state,
coalesce(ac.zip,'na') as zip,
coalesce(crtt.value,'na') as restitutiontype,
coalesce(tblsex.value,'na') as gender,
coalesce(eth.value,'na') as ethnicity,
mbt.value as militarybranch,
mst.value as militarystatus,
coalesce(secagentnum,'not recorded') as secagentnum,
l.locationnum, l.name as store,
coalesce(l.address,'na') as locationaddress,
coalesce(l.city,'na') as locationcity,
coalesce(l.state,'na') as locationstate,
coalesce(l.zip,'na') as locationzip,
d .districtnum,
d .districtname as district, r.regionnum,
r.regionname as region, dv.divisionnum,
dv.divisionname as division,
case when c.apprehdate is null then c.opendate
else c.apprehdate
end as apprehdate,
to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year,
to_char( coalesce(c.apprehdate, c.opendate),'q') as
quarter,
to_char( coalesce(c.apprehdate, c.opendate),'MM') as
month,
to_char( coalesce(c.apprehdate, c.opendate),'D') as
weekday,
to_char( coalesce(c.apprehdate, c.opendate),'WW') as week,
to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as
time,
coalesce(c.sourcedocnum,'none') as sourcedocnum,
case coalesce(c.sourcemodule,'n')
when 'n' then 'none'
when 'i' then 'incident'
when 'g' then 'general investigation'
when 'e' then 'employee investigation'
else 'none'
end as sourcemodule,
case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber,
'no')
when 'nono' then 'no' else 'yes' end as civilcase,
coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na')
as lpmanager
from tblcase c left outer join
tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1=
a.presentationid left outer join
tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1=
m.presentationid left outer join
tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1=
m2.presentationid left outer join
tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum
and 1= cs.presentationid
inner join
tbllocation l
left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and
l.clientnum = lpr.clientnum and 1= lpr.presentationid
on c.clientnum = l.clientnum and
c.locationid = l.locationid
inner join
tbldistrict d on
c.clientnum = d.clientnum and
l.districtid = d.districtid and
l.regionid = d.regionid and
l.divisionid = d.divisionid
inner join
tblregion r on
c.clientnum = r.clientnum and
l.regionid = r.regionid and
l.divisionid = r.divisionid
inner join
tbldivision dv on
c.clientnum = dv.clientnum and
l.divisionid = dv.divisionid
left outer join
tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum =
crtt.clientnum and 1= crtt.presentationid left outer join
tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join
tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left
outer join
tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and
1= ct.presentationid left outer join
tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and
1= u1.presentationid left outer join
tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1=
u2.presentationid left outer join
tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum =
ot.clientnum and 1= ot.presentationid left outer join
tblassociate ac
on c.subjectid = ac.associateid and c.clientnum = ac.clientnum
left outer join tblethnicity eth on ac.ethnicityid = eth.id and
ac.clientnum = eth.clientnum and 1= eth.presentationid
left outer join tblsex on ac.sexid = tblsex.id and 1=
tblsex.presentationid
left outer join tblmilitarybranch mbt on ac.militarybranchid =
mbt.id and ac.clientnum = mbt.clientnum and 1= mbt.presentationid
left outer join tblmilitarystatus mst on ac.militarystatusid =
mst.id and ac.clientnum = mst.clientnum and 1= mst.presentationid
left outer join tblpatrontype pt on ac.patrontypeid = pt.id and
ac.clientnum = pt.clientnum and 1= pt.presentationid
left outer join tbljobtitle jt on ac.jobtitleid = jt.id and
ac.clientnum = jt.clientnum and 1= jt.presentationid
left join
tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and
c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum
left join tblcdacases cdacase
on c.clientnum = cdacase.clientnum and c.casenum =
cdacase.clicasenumber
where c.isdeleted = false and c.subjecttypeid = 6/* and c.clientnum =
'waz'*/
/*other 7*/
union
select c.clientnum,c.casenum, c.casereferencenum,coalesce
((select sum(quantity * amount)
as merchandiseamount
from tblmerchandise m
where m.caseid = c.caseid and m.clientnum=c.clientnum), 0)
|| coalesce(c.totaladmitted, 0) as casevalue,
coalesce(c.totaladmitted, 0) as admitted,
coalesce(c.totalrecovery, 0) as recovered,
coalesce(c.ageatcase, 0) as ageatcase,
1 as numberofcase, coalesce(ct.value,'na') as casetype,
s.value as status, c.opendate, c.closedate,
a.value as action, u2.completename as closebyuser,
cs.value as casesource, m.value as method, m2.value as method2,
c.reportingagentfirstinitial, c.reportingagentlastname,
case when c.monthsemployedatcase is null then 'na'
else cast(c.monthsemployedatcase as varchar(3))
end as monthsemployedatcase,
u1.completename as createdby, st.value as subjecttype,
ot.value as offensetype,
otherpt.value as patrontype,
'na' as jobtitle,
0 as testscore,
coalesce(other.firstname,'na') as firstname,
coalesce(other.lastname,'na') as lastname,
coalesce(other.address,'na') as address,
coalesce(other.city,'na') as city,
coalesce(other.state,'na') as state,
coalesce(other.zip,'na') as zip,
coalesce(crtt.value,'na') as restitutiontype,
coalesce(tblsex.value,'na') as gender,
coalesce(eth.value,'na') as ethnicity,
othermbt.value as militarybranch,
othermst.value as militarystatus,
coalesce(secagentnum,'not recorded') as secagentnum,
l.locationnum, l.name as store,
coalesce(l.address,'na') as locationaddress,
coalesce(l.city,'na') as locationcity,
coalesce(l.state,'na') as locationstate,
coalesce(l.zip,'na') as locationzip,
d .districtnum,
d .districtname as district, r.regionnum,
r.regionname as region, dv.divisionnum,
dv.divisionname as division,
case when c.apprehdate is null then c.opendate
else c.apprehdate
end as apprehdate,
to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year,
to_char( coalesce(c.apprehdate, c.opendate),'q') as
quarter,
to_char( coalesce(c.apprehdate, c.opendate),'MM') as
month,
to_char( coalesce(c.apprehdate, c.opendate),'D') as
weekday,
to_char( coalesce(c.apprehdate, c.opendate),'WW') as week,
to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as
time,
coalesce(c.sourcedocnum,'none') as sourcedocnum,
case coalesce(c.sourcemodule,'n')
when 'n' then 'none'
when 'i' then 'incident'
when 'g' then 'general investigation'
when 'e' then 'employee investigation'
else 'none'
end as sourcemodule,
case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber,
'no')
when 'nono' then 'no' else 'yes' end as civilcase,
coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na')
as lpmanager
from tblcase c left outer join
tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1=
a.presentationid left outer join
tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1=
m.presentationid left outer join
tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1=
m2.presentationid left outer join
tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum
and 1= cs.presentationid
inner join
tbllocation l
left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and
l.clientnum = lpr.clientnum and 1= lpr.presentationid
on c.clientnum = l.clientnum and
c.locationid = l.locationid
inner join
tbldistrict d on
c.clientnum = d.clientnum and
l.districtid = d.districtid and
l.regionid = d.regionid and
l.divisionid = d.divisionid
inner join
tblregion r on
c.clientnum = r.clientnum and
l.regionid = r.regionid and
l.divisionid = r.divisionid
inner join
tbldivision dv on
c.clientnum = dv.clientnum and
l.divisionid = dv.divisionid
/*
left outer join
tbllocation l left outer join
tbldistrict d left outer join
tblregion r left outer join
tbldivision dv on r.divisionid = dv.divisionid and r.clientnum =
dv.clientnum on
d .regionid = r.regionid and d.clientnum = r.clientnum on l.districtid = d
.districtid and l.clientnum = d.clientnum on
c.locationid = l.locationid and c.clientnum = l.clientnum
*/
left outer join
tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum =
crtt.clientnum and 1= crtt.presentationid left outer join
tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join
tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left
outer join
tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and
1= ct.presentationid left outer join
tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and
1= u1.presentationid left outer join
tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1=
u2.presentationid left outer join
tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum =
ot.clientnum and 1= ot.presentationid left outer join
tblotherperson other
on c.subjectid = other.otherpersonid and c.clientnum = other.clientnum
left outer join tblethnicity eth on other.ethnicityid = eth.id and
other.clientnum = eth.clientnum and 1= eth.presentationid
left outer join tblsex on other.sexid = tblsex.id and 1=
tblsex.presentationid
left outer join tblmilitarybranch othermbt on other.militarybranchid
= othermbt.id and other.clientnum = othermbt.clientnum and 1=
othermbt.presentationid
left outer join tblmilitarystatus othermst on other.militarystatusid
= othermst.id and other.clientnum = othermst.clientnum and 1=
othermst.presentationid
left outer join tblpatrontype otherpt on other.patrontypeid =
otherpt.id and other.clientnum = otherpt.clientnum and 1=
otherpt.presentationid
left join
tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and
c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum
left join tblcdacases cdacase
on c.clientnum = cdacase.clientnum and c.casenum =
cdacase.clicasenumber
where c.isdeleted = false and c.subjecttypeid = 7 /*and c.clientnum =
'waz'*/
;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
