Hi,

We are having a performance problem with our database. The problem
exists when we include a constraint in GCTBALLOT. The constraint is as
follows:

alter table GCTBALLOT
   add constraint FK_GCTBALLOT_GCTWEBU foreign key (GCTWEBU_SRL)
      references GCTWEBU (SRL)
      on delete restrict on update restrict;

The two tables that we insert into are the following:

GCTBALLOT:

                                          Table "cbcca.gctballot"

      Column      |            Type             |                      
  Modifiers
------------------+-----------------------------+-----------------------------------------------------------
 srl              | integer                     | not null default
nextval('cbcca.gctballot_srl_seq'::text)
 gctbwindow_srl   | numeric(12,0)               | not null
 gctcandidate_srl | numeric(12,0)               | not null
 gctwebu_srl      | numeric(12,0)               |
 gctphoneu_srl    | numeric(12,0)               |
 ballot_time      | timestamp without time zone | not null
 ip_addr          | character varying(15)       |
Indexes:
    "pk_gctballot" primary key, btree (srl)
    "i1_gctballot_webusrl" btree (gctwebu_srl)
Foreign-key constraints:
    "fk_gctbwindow_gctballot" FOREIGN KEY (gctbwindow_srl) REFERENCES
gctbwindow(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
    "fk_gctcandidate_gctballot" FOREIGN KEY (gctcandidate_srl)
REFERENCES gctcandidate(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
    "fk_gctphoneu_gctballot" FOREIGN KEY (gctphoneu_srl) REFERENCES
gctphoneu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT

with the extra constraint:

"fk_gctballot_gctwebu" FOREIGN KEY (gctwebu_srl) REFERENCES
gctwebu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT



GCTWEBU:

                                          Table "cbcca.gctwebu"
     Column      |            Type             |                       
Modifiers
-----------------+-----------------------------+---------------------------------------------------------
 srl             | integer                     | not null default
nextval('cbcca.gctwebu_srl_seq'::text)
 gctlocation_srl | numeric(12,0)               | not null
 gctagerange_srl | numeric(12,0)               | not null
 email           | character varying(255)      | not null
 uhash           | character varying(255)      | not null
 sex             | character varying(1)        | not null
 created_time    | timestamp without time zone | not null
Indexes:
    "pk_gctwebu" primary key, btree (srl)
    "i1_gctwebu_email" unique, btree (email)
Foreign-key constraints:
    "fk_gctagerang_gctwebu" FOREIGN KEY (gctagerange_srl) REFERENCES
gctagerange(srl) ON UPDATE RESTRICT ON DELETE RESTRICT
    "fk_gctwebu_gctlocation" FOREIGN KEY (gctlocation_srl) REFERENCES
gctlocation(srl) ON UPDATE RESTRICT ON DELETE RESTRICT


To begin, GCTBALLOT has 6122546 rows and GCTWEBU has 231444 rows.

Now when we try and insert 100 entries into GCTBALLOT with the extra
constraint it 
takes: 37981 milliseconds

Also, when we try and insert 100 entries into GCTBALLOT with the extra
constraint, 
but insert 'null' into the column gctwebu_srl it takes: 286
milliseconds

However when we try and insert 100 entries into GCTBALLOT without the
extra constraint (no foreign key between GCTBALLOT & GCTWEBU)
it takes: 471 milliseconds


In summary, inserting into GCTBALLOT without the constraint or
inserting null for 
gctwebu_srl in GCTBALLOT gives us good performance.  However, inserting
into GCTBALLOT
with the constraint and valid gctwebu_srl values gives us poor
performance.

Also, the insert we use is as follows:

INSERT INTO GCTBALLOT  (gctbwindow_srl, gctcandidate_srl, gctwebu_srl,
gctphoneu_srl, 
ballot_time, ip_addr) VALUES (CBCCA.gcf_getlocation(?), ?,
CBCCA.gcf_validvoter(?,?), 
null, ?, ?);

NOTE: "gcf_validvoter" find 'gctweb_srl' value

"
CREATE OR REPLACE FUNCTION gcf_validvoter (VARCHAR, VARCHAR) 
          RETURNS NUMERIC AS '
DECLARE
  arg1       ALIAS FOR $1;
  arg2       ALIAS FOR $2;
  return_val NUMERIC;
BEGIN
  SELECT SRL INTO return_val
  FROM   gctwebu
  WHERE  EMAIL = arg1
  AND    UHASH = arg2;

  RETURN return_val;
END;
' LANGUAGE plpgsql;
"


Where the question marks are filled in with values in our java code.

We are puzzled as to why there is this difference in performance when
inserting b/c we 
believe that we have indexed all columns used by this constraint. And
we realize that 
inserting 'null' into GCTBALLOT doesn't use this constraint b/c no look
up is necessary.
So this causes good performance.  Why is it that when we use this
constraint that
the performance is effected so much?


Thanks


P.S. Even we added an index on 'gctwebu_srl' column and did 
1- "Analyzed ALL TABLES"
2- "analyze GCTBALLOT(gctwebu_srl);"

but still have the same problem!


Fri, 13 Aug 2004 10:43:43 -0400
"Arash Zaryoun" <Arash_Zaryoun@CBC.CA>
Weird Database Performance problem!
Arash Zaryoun wrote:
> Hi,
> 
> We are having a performance problem with our database. The problem
> exists when we include a constraint in GCTBALLOT. The constraint is as
> follows:
> 
> alter table GCTBALLOT
>    add constraint FK_GCTBALLOT_GCTWEBU foreign key (GCTWEBU_SRL)
>       references GCTWEBU (SRL)
>       on delete restrict on update restrict;
> 
> The two tables that we insert into are the following:

> GCTBALLOT:
>  gctwebu_srl      | numeric(12,0)               |

> GCTWEBU:
>  srl             | integer                     | not null default

Your types don't match. You have a numeric referencing an integer. PG 
probably isn't using the index (it's smarter about this in 8.0 iirc).

HTH
-- 
   Richard Huxton
   Archonet Ltd

Fri, 13 Aug 2004 16:20:04 +0100
Richard Huxton <dev@archonet.com>
Re: Weird Database Performance problem!


	ReiserFS 4 is (will be) a filesystem that implements transactions.

	Are there any plans in a future Postgresql version to support a special  
fsync method for Reiser4 which will use the filesystem's transaction  
engine, instead of an old kludge like fsync(), with a possibility of  
vastly enhanced performance ?

	Is there also a possibility to tell Postgres : "I don't care if I lose 30  
seconds of transactions on this table if the power goes out, I just want  
to be sure it's still ACID et al. compliant but you can fsync less often  
and thus be faster" (with a possibility of setting that on a per-table  
basis) ?

	Thanks.

Fri, 13 Aug 2004 18:12:34 +0200
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
	<lists@boutiquenumerique.com>
Reiser4
Pierre,

> 	Are there any plans in a future Postgresql version to support a special  
> fsync method for Reiser4 which will use the filesystem's transaction  
> engine, instead of an old kludge like fsync(), with a possibility of  
> vastly enhanced performance ?

I don't know of any such in progress right now.  Why don't you start it?  It 
would have to be an add-in since we support 28 operating systems and Reiser 
is AFAIK Linux-only, but it sounds like an interesting experiment.

> 	Is there also a possibility to tell Postgres : "I don't care if I lose 30  
> seconds of transactions on this table if the power goes out, I just want  
> to be sure it's still ACID et al. compliant but you can fsync less often  
> and thus be faster" (with a possibility of setting that on a per-table  
> basis) ?

Not per-table, no, but otherwise take a look at the Background Writer feature 
of 8.0.

-- 
-Josh Berkus
 "A developer of Very Little Brain"
 Aglio Database Solutions
 San Francisco


Fri, 13 Aug 2004 11:01:52 -0700
Josh Berkus <josh@agliodbs.com>
Re: Reiser4
Pierre-Fr�d�ric Caillaud wrote:
> 	Is there also a possibility to tell Postgres : "I don't care if I lose 30  
> seconds of transactions on this table if the power goes out, I just want  
> to be sure it's still ACID et al. compliant but you can fsync less often  
> and thus be faster" (with a possibility of setting that on a per-table  
> basis) ?

I have been thinking about this.  Informix calls it buffered logging and
it would be a good feature.

Added to TODO:

* Allow buffered WAL writes and fsync

  Instead of guaranteeing recovery of all committed transactions, this
  would provide improved performance by delaying WAL writes and fsync
  so an abrupt operating system restart might lose a few seconds of
  committed transactions but still be consistent.  We could perhaps
  remove the 'fsync' parameter (which results in an an inconsistent
  database) in favor of this capability.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 13 Aug 2004 21:30:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PERFORM] Reiser4
Josh Berkus wrote:
> Pierre,
> 
> > 	Are there any plans in a future Postgresql version to support a special  
> > fsync method for Reiser4 which will use the filesystem's transaction  
> > engine, instead of an old kludge like fsync(), with a possibility of  
> > vastly enhanced performance ?
> 
> I don't know of any such in progress right now.  Why don't you start it?  It 
> would have to be an add-in since we support 28 operating systems and Reiser 
> is AFAIK Linux-only, but it sounds like an interesting experiment.
> 
> > 	Is there also a possibility to tell Postgres : "I don't care if I lose 30  
> > seconds of transactions on this table if the power goes out, I just want  
> > to be sure it's still ACID et al. compliant but you can fsync less often  
> > and thus be faster" (with a possibility of setting that on a per-table  
> > basis) ?
> 
> Not per-table, no, but otherwise take a look at the Background Writer feature 
> of 8.0.

Actually the fsync of WAL is the big performance issue here.  I added a
TODO item about it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 13 Aug 2004 21:31:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Reiser4
Bruce Momjian wrote:
> Pierre-Frï¿½dï¿½ric Caillaud wrote:
> > 	Is there also a possibility to tell Postgres : "I don't care if I
> > lose 30 seconds of transactions on this table if the power goes
> > out, I just want to be sure it's still ACID et al. compliant but
> > you can fsync less often and thus be faster" (with a possibility of
> > setting that on a per-table basis) ?

Then it would be "ACI" compliant.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Sat, 14 Aug 2004 12:57:37 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: [PERFORM] Reiser4