Query performance problem - Mailing list pgsql-general

Dear psqlers,

I need your help!

I administer/develop an open source PHP accounting software project (webERP)
that was originally mysql only. Since Christmas I and another member of the
team lower cased all the sql and changed some elements of the SQL to allow it
to use postgres as well. All appears to work beautifully with just a single
but important exception.....

I am using PostgreSQL 7.4.6 running on :5432 on Mandrake 10.1

We wish to avoid lock in to a single database and have a single code base. We
elected to commonise the sql to ansi standard so far as possible in
preference to using stored procs. Whilst this will mean some compromises to
the performance, the sql is quite simple througout as this is a design goal
and I like having the sql inside the code for readability purposes ie its all
in one place. (I know I am wrong from a computer scientist's perspective!)

There are 2 tables used in the sql we need to optimise .....

CREATE TABLE chartdetails (
    accountcode integer DEFAULT 0 NOT NULL,
    period integer DEFAULT 0 NOT NULL,
    budget double precision DEFAULT (0)::double precision NOT NULL,
    actual double precision DEFAULT (0)::double precision NOT NULL,
    bfwd double precision DEFAULT (0)::double precision NOT NULL,
    bfwdbudget double precision DEFAULT (0)::double precision NOT NULL
);


CREATE INDEX idxperiod ON chartdetails USING btree (period);
ALTER TABLE ONLY chartdetails
    ADD CONSTRAINT chartdetails_pkey PRIMARY KEY (accountcode, period);
ALTER TABLE ONLY chartdetails
    ADD CONSTRAINT cnt001251 FOREIGN KEY (accountcode) REFERENCES
chartmaster(accountcode);
ALTER TABLE ONLY chartdetails
    ADD CONSTRAINT cnt001252 FOREIGN KEY (period) REFERENCES
periods(periodno);



AND the second table:


CREATE TABLE gltrans (
    counterindex serial NOT NULL,
    "type" integer DEFAULT 0 NOT NULL,
    typeno bigint DEFAULT (1)::bigint NOT NULL,
    chequeno integer DEFAULT 0 NOT NULL,
    trandate date,
    periodno integer DEFAULT 0 NOT NULL,
    account integer DEFAULT 0 NOT NULL,
    narrative text DEFAULT ''::text NOT NULL,
    amount double precision DEFAULT (0)::double precision NOT NULL,
    posted integer DEFAULT 0 NOT NULL,
    jobref text DEFAULT ''::text NOT NULL
);


CREATE INDEX idxaccount ON gltrans USING btree (account);
CREATE INDEX idxchequeno ON gltrans USING btree (chequeno);
CREATE INDEX idxgtperiodno ON gltrans USING btree (periodno);
CREATE INDEX idxposted ON gltrans USING btree (posted);
CREATE INDEX idxgttrandate ON gltrans USING btree (trandate);
CREATE INDEX idxgttypeno ON gltrans USING btree (typeno);
CREATE INDEX idxtype_and_number ON gltrans USING btree ("type", typeno);
CREATE INDEX idxgtjobref ON gltrans USING btree (jobref);
ALTER TABLE ONLY gltrans
    ADD CONSTRAINT gltrans_pkey PRIMARY KEY (counterindex);
ALTER TABLE ONLY gltrans
    ADD CONSTRAINT cnt001296 FOREIGN KEY (account) REFERENCES
chartmaster(accountcode);
ALTER TABLE ONLY gltrans
    ADD CONSTRAINT cnt001297 FOREIGN KEY ("type") REFERENCES systypes(typeid);
ALTER TABLE ONLY gltrans
    ADD CONSTRAINT cnt001298 FOREIGN KEY (periodno) REFERENCES
periods(periodno);


So there is a chartdetail record for every period for every general ledger
account.  So if there are 5 years x 12 periods (months) and 200 general
ledger accounts this table will be 12,000 records.

There is a gltrans record for every side of a journal entry. This can get to
be quite a significant table - easily more than 200,000 per annum - depending
on the size of the business obviously.


Can anyone tell me why does the following code chokes ... literally - this
works almost invisbly under mysql - pg takes more than an hour even on a very
small 30 record database.

(The table chartmaster is just a list of general ledger accounts accountcode
and accountdescription. PK = accountcode)


    $ChartAccounts = DB_query('SELECT accountcode FROM chartmaster',$db);

    While ($AccountRow = DB_fetch_array($ChartAccounts)){

        for ($PeriodNo=$CreateFrom;$PeriodNo <= $CreateTo;$PeriodNo++) {

            echo '<LI>' . _('Period Number') . ' ' . $PeriodNo . '</LI>';

            // Check if there is an chart details record set up
            $sql = 'SELECT count(*) FROM chartdetails
                    WHERE accountcode='.$AccountRow['accountcode'].'
                    AND period=' . $PeriodNo;
            $InsChartDetails = DB_query($sql,$db,'','','',false);
            $CountRows = DB_fetch_row($InsChartDetails);
            $AccountExistsAlready = $CountRows[0];
            DB_free_result($InsChartDetails);
            if(! $AccountExistsAlready) {
                $sql = 'INSERT INTO chartdetails (accountcode,
                            period)
                    VALUES (' . $AccountRow['accountcode'] . ',
                        ' . $PeriodNo . ')';
                $InsChartDetails = DB_query($sql,$db);
                DB_free_result($InsChartDetails);
            }

        }

    /*Now run through each of the new chartdetail records created for each
account and update them with the B/Fwd and B/Fwd budget no updates would be
required where there were previously no chart details set up ie
FirstPeriodPostedTo > 0 */

        for ($PeriodNo=$CreateFrom;$PeriodNo<=$CreateTo; $PeriodNo++) {

            $sql = 'SELECT accountcode,
                    period,
                    actual + bfwd AS cfwd,
                    budget + bfwdbudget AS cfwdbudget
                FROM chartdetails WHERE period =' . ($PeriodNo  - 1);
            $ChartDetailsCFwd = DB_query($sql,$db);

            while ($myrow = DB_fetch_array($ChartDetailsCFwd)){

                $sql = 'UPDATE chartdetails SET bfwd =' . $myrow['cfwd'] . ',
                                bfwdbudget =' . $myrow['cfwdbudget'] . '
                        WHERE accountcode = ' . $myrow['accountcode'] . '
                        AND period >=' . $PeriodNo;
                $UpdChartDetails = DB_query($sql,$db, '', '', '', false);
                DB_free_result($UpdChartDetails);
            }
            DB_free_result($ChartDetailsCFwd);
        }

    }


function DB_query ($SQL,
        &$Conn,
        $ErrorMessage='',
        $DebugMessage= '',
        $Transaction=false,
        $TrapErrors=true){

    global $debug;

    $result = pg_query($Conn, $SQL);
    if ($DebugMessage == '') {
        $DebugMessage = _('The SQL that failed was:');
    }
    //if (DB_error_no($Conn) != 0){
    if ( !$result AND $TrapErrors){
        prnMsg($ErrorMessage.'<BR>' . DB_error_msg($Conn),'error', _('DB ERROR:'));
        if ($debug==1){
            echo '<BR>' . $DebugMessage. "<BR>$SQL<BR>";
        }
        if ($Transaction){
            $SQL = 'rollback';
            $Result = DB_query($SQL,$Conn);
            if (DB_error_no($Conn) !=0){
                prnMsg('<br />'.  _('Error Rolling Back Transaction!!'), '', _('DB
DEBUG:') );
            }
        }
        if ($TrapErrors){
            include('includes/footer.inc');
            exit;
        }
    }
    return $result;

}


I am hoping that someone will be able to see an alternative simpler method or
suggest a method of indexing the pg tables to optmise the required queries. I
would appreciate any help here men.

Many thanks in advance ....

--
Phil Daintree
webERP Project Admin