pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Deleting one record from a table taking 17s.
5+ messages / 3 participants
[nested] [flat]

* Deleting one record from a table taking 17s.
@ 2003-06-20 15:53 Yusuf <yusuf0478@netscape.net>
  2003-06-20 16:06 ` Re: Deleting one record from a table taking 17s. Rod Taylor <rbt@rbt.ca>
  0 siblings, 1 reply; 5+ messages in thread

From: Yusuf @ 2003-06-20 15:53 UTC (permalink / raw)
  To: pgsql-performance

Why would the following query take soo long to run? What does 28.12 msec represent, since the total running time is 
16801.86 ms.

The table phoneinfo has a primary key called phoneinfo_id and the table has 400 000 records.

mydb=#explain analyze delete from phoneinfo where phoneinfo_id = 85723;

                                                         QUERY PLAN

---------------------------------------------------------------------------------------------------------------------
-----
  Index Scan using phoneinfo_pkey on phoneinfo  (cost=0.00..3.81 rows=1 width=6) (actual time=27.93..27.94 rows=1 loop
s=1)
    Index Cond: (phoneinfo_id = 85723)
  Total runtime: 28.12 msec
(3 rows)

Time: 16801.86 ms

BTW, I have \timing on.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Deleting one record from a table taking 17s.
  2003-06-20 15:53 Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
@ 2003-06-20 16:06 ` Rod Taylor <rbt@rbt.ca>
  2003-06-20 16:59   ` Re: Deleting one record from a table taking 17s. Tom Lane <tgl@sss.pgh.pa.us>
  2003-06-20 17:06   ` Re: Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
  0 siblings, 2 replies; 5+ messages in thread

From: Rod Taylor @ 2003-06-20 16:06 UTC (permalink / raw)
  To: Yusuf <yusuf0478@netscape.net>; +Cc: pgsql-performance

On Fri, 2003-06-20 at 15:53, Yusuf wrote:
> Why would the following query take soo long to run? What does 28.12 msec represent, since the total running time is 
> 16801.86 ms.

I'd hazard to guess that you have a whole slew of foreign keys cascading
to delete, update, or check many rows from other tables.

Those are not represented in the explains at the moment.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Deleting one record from a table taking 17s.
  2003-06-20 15:53 Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
  2003-06-20 16:06 ` Re: Deleting one record from a table taking 17s. Rod Taylor <rbt@rbt.ca>
@ 2003-06-20 16:59   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2003-06-20 16:59 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Yusuf <yusuf0478@netscape.net>; pgsql-performance

Rod Taylor <rbt@rbt.ca> writes:
> On Fri, 2003-06-20 at 15:53, Yusuf wrote:
>> Why would the following query take soo long to run? What does 28.12 msec =
> represent, since the total running time is=20
>> 16801.86 ms.

> I'd hazard to guess that you have a whole slew of foreign keys cascading
> to delete, update, or check many rows from other tables.

Either that or some other AFTER trigger(s) that are taking lots of time.
Those fire after the end of the statement, so EXPLAIN's measurement of
runtime fails to include them.

Given that this query appears to have deleted only one row, though, you
sure seem to have a mighty slow trigger.  If it's an FK, perhaps you are
missing an index on the referencing column?  The system doesn't force
you to have an index on that side of an FK, but it's generally a good
idea.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Deleting one record from a table taking 17s.
  2003-06-20 15:53 Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
  2003-06-20 16:06 ` Re: Deleting one record from a table taking 17s. Rod Taylor <rbt@rbt.ca>
@ 2003-06-20 17:06   ` Yusuf <yusuf0478@netscape.net>
  2003-06-20 22:04     ` Re: Deleting one record from a table taking 17s. Rod Taylor <rbt@rbt.ca>
  1 sibling, 1 reply; 5+ messages in thread

From: Yusuf @ 2003-06-20 17:06 UTC (permalink / raw)
  To: pgsql-performance



rbt@rbt.ca wrote:
> On Fri, 2003-06-20 at 15:53, Yusuf wrote:
> 
>>Why would the following query take soo long to run? What does 28.12 msec represent, since the total running time is 
>>16801.86 ms.
> 
> 
> I'd hazard to guess that you have a whole slew of foreign keys cascading
> to delete, update, or check many rows from other tables.
> 
> Those are not represented in the explains at the moment.
> 

That's what I thought at first, so I dropped the foreign key constraints.  The table is referenced by 2 tables, one of 
which has around 200 000 records and the other has 0 records.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Deleting one record from a table taking 17s.
  2003-06-20 15:53 Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
  2003-06-20 16:06 ` Re: Deleting one record from a table taking 17s. Rod Taylor <rbt@rbt.ca>
  2003-06-20 17:06   ` Re: Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
@ 2003-06-20 22:04     ` Rod Taylor <rbt@rbt.ca>
  0 siblings, 0 replies; 5+ messages in thread

From: Rod Taylor @ 2003-06-20 22:04 UTC (permalink / raw)
  To: Yusuf <yusuf0478@netscape.net>; +Cc: pgsql-performance

On Fri, 2003-06-20 at 13:06, Yusuf wrote:
> rbt@rbt.ca wrote:
> > On Fri, 2003-06-20 at 15:53, Yusuf wrote:
> > 
> >>Why would the following query take soo long to run? What does 28.12 msec represent, since the total running time is 
> >>16801.86 ms.
> > 
> > 
> > I'd hazard to guess that you have a whole slew of foreign keys cascading
> > to delete, update, or check many rows from other tables.
> > 
> > Those are not represented in the explains at the moment.
> > 
> 
> That's what I thought at first, so I dropped the foreign key constraints.  The table is referenced by 2 tables, one of 
> which has around 200 000 records and the other has 0 records.

Hmm... EXPLAIN ANALYZE your select again, but join both of those
referenced tables to the appropriate columns.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-06-20 22:04 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-20 15:53 Deleting one record from a table taking 17s. Yusuf <yusuf0478@netscape.net>
2003-06-20 16:06 ` Rod Taylor <rbt@rbt.ca>
2003-06-20 16:59   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-20 17:06   ` Yusuf <yusuf0478@netscape.net>
2003-06-20 22:04     ` Rod Taylor <rbt@rbt.ca>

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