On Jun 21, 2006, at 5:53 PM, Ron St-Pierre wrote:
Jim C. Nasby wrote:
On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote:
We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM,
raid 4, RHEL, postgres 8.1) and ported our old database over to
it (single cpu,
RAID *4*?
oops, raid 5 (but we are getting good io throughput...)
Just remember that unless you have a really good battery-backed
controller, writes to RAID5 pretty much suck.
BEGIN
TRUNCATE stock.datacount;
FOR rec IN SELECT itemID, item, hexValue FROM
stock.activeitem LOOP
histdate := (SELECT updatedate FROM stock.historical s
WHERE s.itemID=rec.itemID ORDER BY updatedate DESC LIMIT 1);
IF histdate IS NOT NULL THEN
funddate := (SELECT updatedate FROM stock.funddata s
WHERE s.itemID=rec.itemID);
techdate := (SELECT updatedate FROM
stock.techsignals s WHERE s.itemID=rec.itemID);
IF (histdate <> funddate) OR (histdate <> techdate)
OR (funddate IS NULL) OR (techdate IS NULL) THEN
counter := counter + 1;
outrec.itemID := rec.itemID;
outrec.item := rec.item;
outrec.hexvalue := rec.hexvalue;
RETURN NEXT outrec;
END IF;
END IF;
END LOOP;
INSERT INTO stock.datacount (itemcount) VALUES (counter);
COPY stock.datacount TO ''/tmp/datacount'';
RETURN;
END;
How would I rewrite it to do away with the cursor?
Something like...
SELECT ...
FROM (SELECT a...., f.updatedate AS funddate, t.updatedate AS
techdate, max(updatedate) hist_date
FROM activeitem a
JOIN historical h USING itemid
GROUP BY a...., f.updatedate, t.updatedate) AS a
LEFT JOIN funddate f USING itemid
LEFT JOIN techsignals USING itemid
WHERE f.updatedate <> hist_date OR t.updatedate <> hist_date OR
f.updatedate IS NULL OR t.updatedate IS NULL
;
BTW, there's some trick that would let you include the NULL tests
with the other tests in the WHERE, but I can't remember it off the
top of my head...
"top" shows:
CPU states: cpu user nice system irq softirq
iowait idle
total 5.8% 0.6% 31.2% 0.0% 0.0% 0.5%
61.6%
Mem: 8152592k av, 8143012k used, 9580k free, 0k shrd,
179888k buff
The high system % (if I'm reading this correctly) makes me wonder if
this is some kind of locking issue.
But it's the only postgres process running.
Sure, but PostgreSQL still acquires internal locks.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
