Обсуждение: Query performance problem
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
doubleprecision 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
precisionNOT 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)::bigintNOT NULL, chequeno integer DEFAULT 0 NOT NULL, trandate date, periodno integer DEFAULT 0 NOT NULL,
accountinteger DEFAULT 0 NOT NULL, narrative text DEFAULT ''::text NOT NULL, amount double precision DEFAULT
(0)::doubleprecision 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") REFERENCESsystypes(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
INTOchartdetails (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',
_('DBERROR:')); 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
-------------------------------------------------------
--
Phil Daintree
webERP Project Admin
> 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.
- You should really use 8.0- How much time toes it takes without the INSERT/UPDATES ?- Please post EXPLAIN ANALYZE of
allthe queries- You could do all that with only two queries
> (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
>
> -------------------------------------------------------
>
On Thursday 17 Mar 2005 1:50 pm, Phil Daintree wrote: > 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 ); although may be not relevant to your question, as i have noticed this before with mysql 'sql', what is the point of having a NOT NULL field that defaults to 0? the whole idea of a NOT NULL field is to have the value filled in compulsorily and having a default of 0 or '' defeats the purpose -- -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
> although may be not relevant to your question, as i have noticed this > before with mysql 'sql', what is the point of having a NOT NULL field > that defaults to 0? the whole idea of a NOT NULL field is to have the > value filled in compulsorily and having a default of 0 or '' defeats > the purpose Well if you define your field as NOT NULL mysql will automatically set it to 0 if you store a NULL anyway, so you might as well specify it in your table definitions so it looks like you wanted it...
Kenneth Gonsalves wrote: > On Thursday 17 Mar 2005 1:50 pm, Phil Daintree wrote: > > >>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 ); > > > although may be not relevant to your question, as i have noticed this > before with mysql 'sql', what is the point of having a NOT NULL field > that defaults to 0? the whole idea of a NOT NULL field is to have the > value filled in compulsorily and having a default of 0 or '' defeats > the purpose Not necessarily. NOT NULL here helps to ensure you can add values together without the risk of a null result. There are plenty of "amount" columns that should be not-null (total spent, total ordered etc). -- Richard Huxton Archonet Ltd
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote: > Not necessarily. NOT NULL here helps to ensure you can add values > together without the risk of a null result. There are plenty of > "amount" columns that should be not-null (total spent, total > ordered etc). that makes sense - but is it necessary to have a not null constraint when there is a default value? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
On Fri, 2005-03-18 at 10:49 +0530, Kenneth Gonsalves wrote: > On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote: > > > Not necessarily. NOT NULL here helps to ensure you can add values > > together without the risk of a null result. There are plenty of > > "amount" columns that should be not-null (total spent, total > > ordered etc). > > that makes sense - but is it necessary to have a not null constraint > when there is a default value? DEFAULT applies to INSERTs, NOT NULL applies to UPDATEs too. gnari
> DEFAULT applies to INSERTs, NOT NULL applies to UPDATEs too. In MySQL it applies to both (ie. if you UPDATE to an invalid value, it sets it to 'something'). NOT NULL without default is useful when you want to be sure you'll never forget to put a value in that column, when there is no meaningful default. Also for foreign keys : what would be the default value of a foreign key ? > > gnari > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Fri, 18 Mar 2005, Kenneth Gonsalves wrote: > On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote: > > > Not necessarily. NOT NULL here helps to ensure you can add values > > together without the risk of a null result. There are plenty of > > "amount" columns that should be not-null (total spent, total > > ordered etc). > > that makes sense - but is it necessary to have a not null constraint > when there is a default value? It's also an added check which prevents you from explicitly setting the value to NULL in an insert or update, since "insert into foo(col1) values (NULL);" shouldn't insert the default value into col1. This is relatively minor generally, but if you have queries whose behavior is broken by NULLs (things using IN/NOT IN for example) it's better to be safe.
Only specifying a default value does not prevent a NULL from being entered
either through accident or ignorance:
jan28-05=# create table test (foo text, foo1 int4 default(0));
CREATE TABLE
jan28-05=# insert into test values('a',1);
INSERT 98685 1
jan28-05=# insert into test values('b',4);
INSERT 98686 1
jan28-05=# insert into test values('c',NULL);
INSERT 98687 1
jan28-05=# insert into test values('d');
INSERT 98688 1
jan28-05=# select * from test;foo | foo1
-----+------a | 1b | 4c |d | 0
(4 rows)
George
----- Original Message -----
From: "Kenneth Gonsalves" <lawgon@thenilgiris.com>
To: "Richard Huxton" <dev@archonet.com>
Cc: <weberp@paradise.net.nz>; <pgsql-sql@postgresql.org>
Sent: Thursday, March 17, 2005 11:19 PM
Subject: Re: [SQL] Query performance problem
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
> Not necessarily. NOT NULL here helps to ensure you can add values
> together without the risk of a null result. There are plenty of
> "amount" columns that should be not-null (total spent, total
> ordered etc).
that makes sense - but is it necessary to have a not null constraint
when there is a default value?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.sourceforge.net
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)