Re: who is best for support? - Mailing list pgsql-admin

Us? :)  Can you provide some more details?

Hardware configuration,
Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids
separate from the OS (pgsql is a raid 10 4 10kdrive array) and /wal is 2 15k
drives). I am running postgres 8 and am using the conf in
/var/lib/pgslq/data to point the data at /pgsql and a link to point the /wal
I set up inside the /pgsql/data folder to /wal.

type of query,

I have cut out most of the query but even the first join makes it go to 27
seconds. In MSSQL with the first two left outers it takes 3 seconds.

explain analyze
SELECT /*(tblaudit.name::text || ' '::text) || tblaudit.version::text AS
audit, tblaudit.active, "substring"((tblsections.section::text || ' '::text)
|| tblsections.sectionname::text, 1, 105) AS section, tblauditstatus.value
AS status, COALESCE(tblaudittypes.value, 'monthly'::character varying) AS
audittype, tblresponseheader.clientnum, tblresponseheader.auditid,
tblresponseheader.responseid, tblresponseheader.division,
tblresponseheader.auditnum, tblresponseheader.customauditnum,
tblresponseheader.dateaudittaken, tblresponseheader.createdby,
to_char(tblresponseheader.dateaudittaken, 'yyyy'::text) AS "year",
to_char(tblresponseheader.dateaudittaken, 'q'::text) AS quarter,
to_char(tblresponseheader.dateaudittaken, 'MM'::text) AS "month",
to_char(tblresponseheader.dateaudittaken, 'D'::text) AS weekday,
to_char(tblresponseheader.dateaudittaken, 'WW'::text) AS week,
to_char(tblresponseheader.dateaudittaken, 'HH24:MI'::text) AS "time",
tbluser.completename AS keyedby, tblresponseheader.datecompleted,
tblresponseheader.dateauditkeyed, tblresponseheader.datekeyingcomplete,
tblresponseheader.pointsavailable AS auditpointsavailable,
tblresponseheader.pointsscored AS auditpointsscored,
        CASE tblresponseheader.pointsavailable
            WHEN 0 THEN 'na'::text
            ELSE COALESCE(to_char(tblresponseheader.pointsscored /
tblresponseheader.pointsavailable * 100::numeric, '99999999'::text),
'na'::text)
        END AS myaudittotalscore,*/ tblresponsesection.sectionid /*,
tblresponsesection.pointsavailable AS sectionpointsavailable,
tblresponsesection.pointsscored AS sectionpointsscored,
tblresponsesection.comments AS sectioncomments, tblresponse_line.questionid,
COALESCE(tblresponse_line.pointsscored, 0::numeric) AS pointsscored,
COALESCE(tblresponse_line.pointsavailable, 0::numeric) AS
questionpointsavailable, tbllocation.locationnum, tbllocation.name AS store,
tblregion.regionnum, tblregion.regionname AS region,
tbldistrict.districtnum, tbldistrict.districtname AS district,
tblresponse_line.y_n, tblresponse_line.answerid,
COALESCE("substring"(tblresponse_line.text_response, 1, 4048), 'na'::text)
AS text_response, tblanswers.answer, tblanswers.answertext,
tblanswers.answerdisplay, "substring"(tblquestions.question::text, 1, 105)
AS question, "substring"(tblquestions.questiondisplay::text, 1, 105) AS
questiondisplay, "substring"(tblquestions.qdescr::text, 1, 105) AS qdescr,
"substring"(tblquestions.qtext::text, 1, 105) AS qtext, tblquestions.qtext
AS qtextlong
*/
   FROM tblresponsesection
 /*  JOIN tblresponse_line ON tblresponsesection.sectionid =
tblresponse_line.sectionid AND tblresponsesection.responseid =
tblresponse_line.responseid AND tblresponsesection.clientnum =
tblresponse_line.clientnum
   JOIN tblresponseheader ON tblresponsesection.responseid =
tblresponseheader.responseid AND tblresponsesection.clientnum =
tblresponseheader.clientnum
   JOIN tbllocation ON tblresponseheader.locationid = tbllocation.locationid
AND tbllocation.clientnum = tblresponseheader.clientnum
   JOIN tbluser ON tblresponseheader.userid = tbluser.userid AND
tbluser.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tbldistrict ON tbllocation.districtid = tbldistrict.districtid AND
tbldistrict.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tblregion ON tbllocation.regionid = tblregion.regionid AND
tblregion.clientnum::bpchar = tblresponseheader.clientnum
   LEFT JOIN tblquestions ON tblresponse_line.clientnum =
tblquestions.clientnum AND tblresponse_line.sectionid =
tblquestions.sectionid AND tblresponse_line.questionid =
tblquestions.questionid AND tblresponseheader.auditid = tblquestions.auditid
   LEFT JOIN tblanswers ON tblresponseheader.auditid = tblanswers.auditid
AND tblresponse_line.clientnum = tblanswers.clientnum AND
tblresponse_line.sectionid = tblanswers.sectionid AND
tblresponse_line.questionid = tblanswers.questionid AND
tblresponse_line.answerid = tblanswers.answerid
   JOIN tblaudit ON tblresponseheader.auditid = tblaudit.auditid AND
tblresponse_line.clientnum = tblaudit.clientnum AND
tblaudit.defaultauditscoretype = 'p'::bpchar
   LEFT JOIN tblsections ON tblresponseheader.auditid = tblsections.auditid
AND tblresponse_line.clientnum = tblsections.clientnum AND
tblresponsesection.sectionid = tblsections.sectionid
   LEFT JOIN tblaudittypes ON tblresponseheader.audittypeid =
tblaudittypes.id AND 1 = tblaudittypes.presentationid AND
tblresponseheader.clientnum = tblaudittypes.clientnum::bpchar
   LEFT JOIN tblauditstatus ON tblresponseheader.statusid =
tblauditstatus.id AND 1 = tblauditstatus.presentationid
  WHERE tblresponseheader.isdeleted = false;*/
where tblresponsesection.clientnum = 'SAKS'

operating system?
Fedora core 3 at the moment, but next week I will get the Redhat AS4 when it
is available (we bought it but wanted the newer Kernel).

Thanks so much for taking a look.
Be happy to furnish conf files etc.
I am not opposed to learning enough to help myself, but also do not mind
getting some hired help, or a combination of both. I got a call from Command
prompt any one have experience with them? They are asking 150.00 an hour and
this may take a bit of time to get it all happy. I am sure I can get some $
budgeted if need be, but I don't want to end up with what I did last time I
called in consulting (was both Microsoft and Dell, when I switched from NT4
to Win2000 my app died in a use of 4 gigs memory in 20 minutes; took me two
weeks to plug all the holes and I had to reload NT 4 in the between time. We
spent like 900.00 on Microsoft and Dell just to be told I had to fix the
application).
----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664