From: Bendik Rognlien Johansen <bendik.johansen@gmail.com>
To: pgsql-performance@postgresql.org
Subject: How to speed up delete
Date: Thu, 7 Jul 2005 13:16:30 +0200
Message-ID: <6F239216-7936-4E62-B708-46DC9171649C@gmail.com> (raw)
Hello,
I was wondering if there is any way to speed up deletes on this table
(see details below)?
I am running few of these deletes (could become many more) inside a
transaction and each one takes allmost a second to complete.
Is it because of the foreign key constraint, or is it something else?
Thanks!
Table "public.contacts"
Column | Type |
Modifiers
-------------+------------------------
+----------------------------------------------------------
id | integer | not null default nextval
('public.contacts_id_seq'::text)
record | integer |
type | integer |
value | character varying(128) |
description | character varying(255) |
priority | integer |
itescotype | integer |
original | integer |
Indexes:
"contacts_pkey" PRIMARY KEY, btree (id)
"contacts_record_idx" btree (record)
Foreign-key constraints:
"contacts_original_fkey" FOREIGN KEY (original) REFERENCES
contacts(id)
dev=# select count(id) from contacts;
count
--------
984834
(1 row)
dev=# explain analyze DELETE FROM contacts WHERE id = 985458;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------
Index Scan using contacts_pkey on contacts (cost=0.00..3.01 rows=1
width=6) (actual time=0.043..0.049 rows=1 loops=1)
Index Cond: (id = 985458)
Total runtime: 840.481 ms
(3 rows)
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: bendik.johansen@gmail.com
Subject: Re: How to speed up delete
In-Reply-To: <6F239216-7936-4E62-B708-46DC9171649C@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