Message-ID: <5040BDD0.9070207@iki.fi>
Date: Fri, 31 Aug 2012 16:36:16 +0300
From: Heikki Linnakangas <hlinnaka@iki.fi>
Reply-To: hlinnaka@iki.fi
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.6esrpre) Gecko/20120817 Icedove/10.0.6
MIME-Version: 1.0
To: John Nash <postgres.dba.needs.help@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: exponential performance decrease in ISD transaction
References: 
 <CA+rSYkSe0oZbaagQeXrg7xP=7tsUn=mkc8vbQ7vYk8noZ7uKKA@mail.gmail.com>
In-Reply-To: 
 <CA+rSYkSe0oZbaagQeXrg7xP=7tsUn=mkc8vbQ7vYk8noZ7uKKA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 31.08.2012 15:27, John Nash wrote:
> Program 1: dbtransfromfile: this program creates a simple table
> consisting of a one int column table. After the creation, the program
> inserts 1000 tuples in the table, which are never deleted, after that
> the program reads a transaction pattern from a given file and executes
> it a number of times determined when the program is launched.
>
> The transaction we are launching is (INSERT/SELECT/DELETE) the following:
>
> insert into T_TEST values (1);select * from T_TEST where
> c1=1000;delete from T_TEST where c1=1;commit;

Sounds like the table keeps growing when rows are inserted and 
subsequently deleted. PostgreSQL doesn't immediately remove deleted 
tuples from the underlying file, but simply marks them as deleted. The 
rows are not physically removed until autovacuum kicks in and cleans it 
up, or the table is vacuumed manually.

I'd suggest creating an index on t_test(c1), if there isn't one already. 
It's not helpful when the table is small, but when the table is bloated 
with all the dead tuples from the deletions, it should help to keep the 
access fast despite the bloat.

- Heikki