pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to speed up delete
4+ messages / 3 participants
[nested] [flat]
* How to speed up delete
@ 2005-07-07 11:16 Bendik Rognlien Johansen <bendik.johansen@gmail.com>
2005-07-07 12:55 ` Re: How to speed up delete PFC <lists@boutiquenumerique.com>
2005-07-07 14:02 ` Re: How to speed up delete Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Bendik Rognlien Johansen @ 2005-07-07 11:16 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to speed up delete
2005-07-07 11:16 How to speed up delete Bendik Rognlien Johansen <bendik.johansen@gmail.com>
@ 2005-07-07 12:55 ` PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 4+ messages in thread
From: PFC @ 2005-07-07 12:55 UTC (permalink / raw)
To: Bendik Rognlien Johansen <bendik.johansen@gmail.com>; pgsql-performance
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 ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to speed up delete
2005-07-07 11:16 How to speed up delete Bendik Rognlien Johansen <bendik.johansen@gmail.com>
@ 2005-07-07 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-07 15:03 ` Re: How to speed up delete Bendik Rognlien Johansen <bendik.johansen@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-07-07 14:02 UTC (permalink / raw)
To: Bendik Rognlien Johansen <bendik.johansen@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to speed up delete
2005-07-07 11:16 How to speed up delete Bendik Rognlien Johansen <bendik.johansen@gmail.com>
2005-07-07 14:02 ` Re: How to speed up delete Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-07-07 15:03 ` Bendik Rognlien Johansen <bendik.johansen@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bendik Rognlien Johansen @ 2005-07-07 15:03 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-07-07 15:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-07 11:16 How to speed up delete Bendik Rognlien Johansen <bendik.johansen@gmail.com>
2005-07-07 12:55 ` PFC <lists@boutiquenumerique.com>
2005-07-07 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-07 15:03 ` Bendik Rognlien Johansen <bendik.johansen@gmail.com>
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