Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to speed up delete

How to speed up delete

From
Bendik Rognlien Johansen
Date:
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

From
PFC
Date:
On Thu, 07 Jul 2005 13:16:30 +0200, Bendik Rognlien Johansen
<bendik.johansen@gmail.com> 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 ?

Re: How to speed up delete

From
Tom Lane
Date:
Bendik Rognlien Johansen <bendik.johansen@gmail.com> 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

From
Bendik Rognlien Johansen
Date:
Thanks!
That took care of it.
On Jul 7, 2005, at 4:02 PM, Tom Lane wrote:

> Bendik Rognlien Johansen <bendik.johansen@gmail.com> 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
>