Thread: problem with 8.0rc1 not using indexes (more info)

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

Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:
> I can post the full function if need be, just let me know.

How about the relevant table and index definitions?

            regards, tom lane