How to speed up delete

Поиск
Список
Период
Сортировка
Искать

How to speed up delete

От:
Bendik Rognlien Johansen <bendik.johansen@gmail.com>
Дата:
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)

Re: How to speed up delete

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bendik Rognlien Johansen  writes:
> 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?

You need an index on "original" to support that FK efficiently.  Check
for references from other tables to this one, too.

			regards, tom lane

Re: How to speed up delete

От:
Bendik Rognlien Johansen <bendik.johansen@gmail.com>
Дата:
Thanks!
That took care of it.
On Jul 7, 2005, at 4:02 PM, Tom Lane wrote:

> Bendik Rognlien Johansen  writes:
>
>> 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?
>>
>
> You need an index on "original" to support that FK efficiently.  Check
> for references from other tables to this one, too.
>
>             regards, tom lane
>

Re: How to speed up delete

От:
PFC <lists@boutiquenumerique.com>
Дата:
On Thu, 07 Jul 2005 13:16:30 +0200, Bendik Rognlien Johansen  
 wrote:

> 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!

	Check your references : on delete, pg needs to find which rows to  
cascade-delete, or set null, or restrict, in the tables which reference  
this one. Also if this table references another I think it will lookup it  
too. Do you have indexes for all this ?
FAQ