Re: exponential performance decrease in ISD transaction

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux