X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 40586528A6
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 27 Jun 2005 08:46:05 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83794-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 27 Jun 2005 11:46:00 +0000 (GMT)
Received: from mail.sto.netlight.se (1-1-10-39a.sh.sth.bostream.se
	[82.182.50.216])
	by svr1.postgresql.org (Postfix) with ESMTP id 1B70252865
	for <pgsql-performance@postgresql.org>;
	Mon, 27 Jun 2005 08:45:56 -0300 (ADT)
Received: from prraxp ([192.168.12.76])
	by mail.sto.netlight.se (8.11.2/8.11.2) with ESMTP id j5RCYd315241;
	Mon, 27 Jun 2005 14:34:39 +0200
From: "Praveen Raja" <praveen.raja@netlight.se>
To: "'Jacques Caron'" <jc@directinfos.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Insert performance vs Table size
Date: Mon, 27 Jun 2005 13:50:13 +0200
Message-ID: <001301c57b0e$643942c0$4c0ca8c0@sto.netlight.se>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.4024
In-Reply-To: 
 <6.2.0.14.0.20050627133508.05df6eb0@pop.interactivemediafactory.net>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200506/494
X-Sequence-Number: 13131

Just to clear things up a bit, the scenario that I'm interested in is a
table with a large number of indexes on it (maybe 7-8). In this scenario
other than the overhead of having to maintain the indexes (which I'm
guessing is the same regardless of the size of the table), does the size
of the table play a role in determining insert performance (and I mean
only insert performance)?

-----Original Message-----
From: Jacques Caron [mailto:jc@directinfos.com] 
Sent: 27 June 2005 13:40
To: Praveen Raja
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Insert performance vs Table size

Hi,

At 13:24 27/06/2005, Praveen Raja wrote:
>I'm wondering if and how the size of a table affects speed of inserts
>into it? What if the table has indexes, does that alter the answer?

Many parameters will affect the result:
- whether there are any indexes (including the primary key, unique 
constraints...) to update or not
- whether there are any foreign keys from or to that table
- the size of the rows
- whether the table (or at least the bits being updated) fit in RAM or
not
- whether the table has "holes" (due to former updates/deletes and
vacuum) 
and how they are placed
- and probably a bunch of other things...

Obviously, if you have an append-only (no updates, no deletes) table
with 
no indexes and no foreign keys, the size of the table should not matter 
much. As soon as one of those conditions is not met table size will have
an 
impact, probably small as long as whatever is needed can be held in RAM,
a 
lot bigger once it's not the case.

Hope that helps,

Jacques.