Thread: having indexes when clearing tables
hi there. i'm trying to clear data in a table but somehow it takes way too much time than i once expected (i'm using the formal 'delete from xxx' type of query.). this table's got several foreign keys and i suspect that is why it takes longer than expected. my question is that would having indexes increase query performance when clearing tables? or could anyone suggest me any ways to increase performance of the query in such cases? thanks very much. regards, nuno
Nuno, It's the foreign keys that point to this table are the problem, not the foreign keys of the table itself. In other words, the child tables for which the table you delete from is the parent... do you have indexes on the foreign keys of the child tables which point to this table ? Those indexes will help... on the other hand, I do have here a few tables which are at the top of the FK chains, and deleting from them takes ages even if all the child tables are indexed for the foreign keys... it all depends on the size of the child tables, the number of them, if the deletion cascades or not and if yes how many rows of the child tables are deleted as well... My top table causes cascaded deletes in as much as 30 other tables, and it actually causes deletion of lots of rows in there, so I do expect it to work slow... maybe that's what you see too. Here we actually do delete first from the child tables and then delete from the parent, so the whole thing can be done in smaller transactions. Cheers, Csaba.
try truncate. just be aware that truncate is being upgraded in 8.2 for better handling of foreign key isues. merlin On 9 Jul 2006 20:35:39 -0700, nuno <wegein@gmail.com> wrote: > hi there. > > i'm trying to clear data in a table but somehow it takes way too much > time than i once expected (i'm using the formal 'delete from xxx' type > of query.). > > this table's got several foreign keys and i suspect that is why it > takes longer than expected. > > my question is that > would having indexes increase query performance when clearing tables? > or could anyone suggest me any ways to increase performance of the > query in such cases? > > thanks very much. > > regards, nuno
On Sun, Jul 09, 2006 at 20:35:39 -0700, nuno <wegein@gmail.com> wrote: > hi there. > > i'm trying to clear data in a table but somehow it takes way too much > time than i once expected (i'm using the formal 'delete from xxx' type > of query.). > > this table's got several foreign keys and i suspect that is why it > takes longer than expected. If there are foreign keys referencing the table you are deleting from, then it can be very benificial to have indexes on those tables. Having an index on the table you are deleting from isn't useful when deleting all of the rows in the table. > > my question is that > would having indexes increase query performance when clearing tables? > or could anyone suggest me any ways to increase performance of the > query in such cases? > > thanks very much. > > regards, nuno > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org