How to speed up delete

From: Bendik Rognlien Johansen <bendik(dot)johansen(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: How to speed up delete
Date: 2005-07-07 11:16:30
Message-ID: 6F239216-7936-4E62-B708-46DC9171649C@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jean-Max Reymond 2005-07-07 12:42:08
Previous Message Niccolo Rigacci 2005-07-07 10:06:58 Re: CURSOR slowes down a WHERE clause 100 times?