From: Heikki Linnakangas <hlinnaka@iki.fi>
To: John Nash <postgres.dba.needs.help@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: exponential performance decrease in ISD transaction
Date: Fri, 31 Aug 2012 16:36:16 +0300
Message-ID: <5040BDD0.9070207@iki.fi> (raw)
In-Reply-To: <CA+rSYkSe0oZbaagQeXrg7xP=7tsUn=mkc8vbQ7vYk8noZ7uKKA@mail.gmail.com>
References: <CA+rSYkSe0oZbaagQeXrg7xP=7tsUn=mkc8vbQ7vYk8noZ7uKKA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnaka@iki.fi, postgres.dba.needs.help@gmail.com
Subject: Re: exponential performance decrease in ISD transaction
In-Reply-To: <5040BDD0.9070207@iki.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox