From: Giulio Cesare Solaroli <giulio.cesare@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Newbie question about degraded performance on delete statement.
Date: Tue, 2 Oct 2007 23:55:27 +0200
Message-ID: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com> (raw)
Hello everybody,
I have just joined the list, as I am experiencing a degradation on
performances on my PostgreSQL instance, and I was looking for some
insights on how to fix/avoid it.
What I have observed are impossibly high time on delete statements on
some tables.
The delete statement is very simple:
delete from table where pk = ?
The explain query report a single index scan on the primary key index,
as expected.
I have run vacuum using the pgAdmin tool, but to no avail.
I have also dropped and recreated the indexes, again without any benefit.
I have later created a copy of the table using the "create table
table_copy as select * from table" syntax.
Matching the configuration of the original table also on the copy
(indexes and constraints), I was able to delete the raws from the new
table with regular performances, from 20 to 100 times faster than
deleting from the original table.
Given this evidence, what are the best practices to fix/avoid this
kind of problems?
I am using PostgreSQL 8.1.4 both on Linux (on a Parallels virtual
machine with a Linux OS) and on Solaris, on a hosted zone; the Solaris
version is running the live DB, while the Linux instance is on my
development machine using a snapshot of the live data.
Thanks for your attention.
Best regards,
Giulio Cesare Solaroli
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: giulio.cesare@gmail.com
Subject: Re: Newbie question about degraded performance on delete statement.
In-Reply-To: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
* 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