Re: [PERFORM] Deleting one record from a table taking 17s.
On Fri, 2003-06-20 at 13:06, Yusuf wrote: > [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Deleting one record from a table taking 17s.
[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Deleting one record from a table taking 17s.
Rod Taylor <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Deleting one record from a table taking 17s.
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 <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
