Re: INSERT performace.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Marc Mitchell" <marcm@eisolution.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Saturday, January 26, 2002 11:06 PM
Subject: Re: [ADMIN] INSERT performace.


> "Marc Mitchell" <marcm@eisolution.com> writes:
> > We have a fresh database and have begun to observe performance
degradation
> > for INSERTs as a table went from empty to 100,000-ish rows.  Initial
> > INSERTs were sub second while after 30k rows, they were 1-3 seconds.
>
> > Note that we have done NO performance tuning yet nor are there any
indexes
> > on the tables.
>
> INSERT per se should be a constant-time operation: stick another tuple
> on the end of the file.  There is undoubtedly something you're not
> telling us about the database schema.  Foreign keys, triggers, rules,
> constraints, something like that?
>

Based on feedback received so far, at least it seems to be confirming that
there is no configuration that needs to be considered for the basic storage
structure of a new table.  I assume tables can be considered heaps with
continuous pointer to the "top" allowing additions to be done with little
fluxuation tied to size.

While there are currently no triggers, rules, or constraints on the tables,
there are defined Foreign keys so perhaps it is the lookups of these that
is contributing to the problem.  This makes a lot of sense.

Marc

> regards, tom lane

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster