From: Jarrod Chesney <jarrod.chesney@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Delete performance
Date: Tue, 31 May 2011 10:08:28 +1000
Message-ID: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com> (raw)
Hi All
My database uses joined table inheritance and my server version is 9.0
Version string PostgreSQL 9.0rc1 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r1 p1.1, pie-0.4.5) 4.4.4, 64-bit
I have about 120,000 records in the table that everything else inherits from, if i truncate-cascaded this table it happens almost instantly. If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it takes close to 10 minutes.
My foreign keys to the base table are all set with "ON DELETE CASCADE". I've looked though all the feilds that relate to the "ID" in the base table and created btree indexes for them.
Can anyone outline what I need to verify/do to ensure i'm getting the best performance for my deletes?
Regards, Jarrod Chesney
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: jarrod.chesney@gmail.com
Subject: Re: Delete performance
In-Reply-To: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@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