I have tracked the problem down to the aggregate COUNT function.
In the below code with the count function in the proc the execution time per 1000 rows of import increases after each commit of 1000.
Please see these screen shots showing the importer running under 7.4.5 and 8.0 rc1
http://www.amsoftwaredesign.com/downloads/pg_screenshots/745_run.PNG
http://www.amsoftwaredesign.com/downloads/pg_screenshots/800rc1_run.PNG
you can see that on the 7.4.5 the times between remain constant, but on the 8.0 they increase after each commit of 1000 rows.
here are the explains for each:
http://www.amsoftwaredesign.com/downloads/pg_screenshots/function_explain_745.PNG
http://www.amsoftwaredesign.com/downloads/pg_screenshots/function_explain_800rc1.PNG
I ended up commenting out sections of the function to see where the problem is and form the below extract you can see what is happening.
The first select grabs the account ID for the account number passed in via accountnumber_IN, the second select graps the security ID from the cusip passed in.
The third select does a count against the positions table, and this is the table that grows as the importer progresses through the raw file.
with the aggregate function in place is when the index for the select appears not to be used. It's strange as when I do a explain on the select by itself it does indicate it is doing a index scan.
It really seems to be a problem with count inside of the function.
My 8.0 server is exactly the same as the 7.4.5 (identicle compaq dl380s wth 2gb of ram and 2..4gzh P4 processors. I simply did a dump of the exact same database on the 7 box and restored it on the 8 box. I also did a index rebuild and vacuum analyszed the 8.0 box and I continually get the same results. The 7.4.5 box finishes the same import routine 25 minutes faster than the 8.0 box.
I won't be able to upgrade my production box until this is resolved.
I can post the full function if need be, just let me know.
SELECT account_id
FROM common.COMMON_ACCOUNT_DETAIL
WHERE account_number = accountnumber_IN AND
administrator <> 'CLD' AND
portfolio_manager <> 'CLD'
INTO varAccountID; SELECT security_id
FROM common.COMMON_SECURITY
WHERE cusip = cusip_IN
INTO varSecurityID;SELECT count(*)
FROM common.COMMON_POSITIONS
WHERE security_id = varSecurityID AND
account_id = varAccountID
INTO varPositionCount;IF (varAccountID IS NOT NULL AND varPositionCount < 1 ) THEN
INSERT INTO common.COMMON_POSITIONS (
account_id,
security_id,
quantity,
cost,
market_value,
holding_percent )
VALUES (
varAccountID,
varSecurityID,
quantity_IN,
cost_IN,
varSecurityMV,
varHoldingPercent );
END IF;Thanks
Tony Caduto
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
