Обсуждение: 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