SEO

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_respons e, 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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Tue, 2005-02-15 at 15:21, Joel Fradkin wrote:
> Us? Can you provide some more details?
>
> Hardware configuration,
> Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids

four processors, or two hyperthreaded cpus? My experience has been that
hyperthreading usually doesn't help, and often hurts performance. This
may have improved with later model 2.6 kernels.

> 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.

Which RAID Controller are you using? Does it have battery backed cache?

> 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.

Are the columns you are joining on the same type? Are they integers
(the fastest) or numerics (slow) or text (also slow)?

Is there a reason you're joining on so many different fields for each
table? If there is a good model of FK relations, that isn't usually
necessary.

IT looks kinda like there's some redundancy in the join on clauses by
how may of them are from all over the place in there.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings