X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4A8358BA2F9; Tue, 15 Feb 2005 21:17:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20691-10; Tue, 15 Feb 2005 21:17:38 +0000 (GMT) Received: from out011.verizon.net (out011pub.verizon.net [206.46.170.135]) by svr1.postgresql.org (Postfix) with ESMTP id 5F4378BA0D7; Tue, 15 Feb 2005 21:17:35 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out011.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050215211734.QPCY28171.out011.verizon.net@jfradkin>; Tue, 15 Feb 2005 15:17:34 -0600 From: "Joel Fradkin" To: "'Marc G. Fournier'" Cc: Subject: Re: who is best for support? Date: Tue, 15 Feb 2005 16:21:17 -0500 Message-ID: <000c01c513a4$4a562110$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable In-Reply-To: <20050215163555.F874@ganymede.hub.org> X-Authentication-Info: Submitted using SMTP AUTH at out011.verizon.net from [66.15.127.235] at Tue, 15 Feb 2005 15:17:34 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.175 tagged_above=0 required=5 tests=AWL, MSGID_DOLLARS X-Spam-Level: *** X-Archive-Number: 200502/132 X-Sequence-Number: 16300 Us? :) Can you provide some more details? =20 Hardware configuration,=20 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,=20 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=20 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,=20 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 =3D tblresponse_line.sectionid AND tblresponsesection.responseid =3D tblresponse_line.responseid AND tblresponsesection.clientnum =3D tblresponse_line.clientnum JOIN tblresponseheader ON tblresponsesection.responseid =3D tblresponseheader.responseid AND tblresponsesection.clientnum =3D tblresponseheader.clientnum JOIN tbllocation ON tblresponseheader.locationid =3D = tbllocation.locationid AND tbllocation.clientnum =3D tblresponseheader.clientnum JOIN tbluser ON tblresponseheader.userid =3D tbluser.userid AND tbluser.clientnum::bpchar =3D tblresponseheader.clientnum JOIN tbldistrict ON tbllocation.districtid =3D tbldistrict.districtid = AND tbldistrict.clientnum::bpchar =3D tblresponseheader.clientnum JOIN tblregion ON tbllocation.regionid =3D tblregion.regionid AND tblregion.clientnum::bpchar =3D tblresponseheader.clientnum LEFT JOIN tblquestions ON tblresponse_line.clientnum =3D tblquestions.clientnum AND tblresponse_line.sectionid =3D tblquestions.sectionid AND tblresponse_line.questionid =3D tblquestions.questionid AND tblresponseheader.auditid =3D = tblquestions.auditid LEFT JOIN tblanswers ON tblresponseheader.auditid =3D = tblanswers.auditid AND tblresponse_line.clientnum =3D tblanswers.clientnum AND tblresponse_line.sectionid =3D tblanswers.sectionid AND tblresponse_line.questionid =3D tblanswers.questionid AND tblresponse_line.answerid =3D tblanswers.answerid JOIN tblaudit ON tblresponseheader.auditid =3D tblaudit.auditid AND tblresponse_line.clientnum =3D tblaudit.clientnum AND tblaudit.defaultauditscoretype =3D 'p'::bpchar LEFT JOIN tblsections ON tblresponseheader.auditid =3D = tblsections.auditid AND tblresponse_line.clientnum =3D tblsections.clientnum AND tblresponsesection.sectionid =3D tblsections.sectionid LEFT JOIN tblaudittypes ON tblresponseheader.audittypeid =3D tblaudittypes.id AND 1 =3D tblaudittypes.presentationid AND tblresponseheader.clientnum =3D tblaudittypes.clientnum::bpchar LEFT JOIN tblauditstatus ON tblresponseheader.statusid =3D tblauditstatus.id AND 1 =3D tblauditstatus.presentationid WHERE tblresponseheader.isdeleted =3D false;*/ where tblresponsesection.clientnum =3D '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