Thread: Delete operation VERY slow...

Hello!

I am trying to delete an entire table. There are about 41,000 rows in
the table (based on count(*)).

I am using the SQL comment: delete from table;

The operation seems to take in the order of hours, rather than seconds
or minutes.

"Explain delete from table" gives me:

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on table  (cost=0.00..3967.74 rows=115374 width=6)
(1 row)


I am using an Intel Pentium D 2.8GHz CPU. My system has about 1.2GB of
RAM. This should be ok... my database isn't that big, I think.


Any ideas why this takes so long and how I could speed this up?

Or alternatively, is there a better way to delete all the contents from
a table?


Thank you!

am  16.06.2006, um 15:58:46 +0900 mailte David Leangen folgendes:
>
> Hello!
>
> I am trying to delete an entire table. There are about 41,000 rows in
> the table (based on count(*)).
>
> I am using the SQL comment: delete from table;

Use TRUNCATE table.


Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Wow! That was almost instantaneous. I can't believe the difference.

The only inconvenience is that I need to remove all the foreign key
constraints before truncating, then put them back after. But I suppose
it is a small price to pay for this incredible optimization.


Thank you!



On Fri, 2006-06-16 at 12:52 +0530, Gourish Singbal wrote:
>
> David,
>
> Truncate table would be a good idea if u want to delete all the data
> in the table.
> You need not perform vacuum in this case since there are no dead rows
> created.
>
> ~gourish
>
>
> On 6/16/06, David Leangen <postgres@leangen.net> wrote:
>
>         Hello!
>
>         I am trying to delete an entire table. There are about 41,000
>         rows in
>         the table (based on count(*)).
>
>         I am using the SQL comment: delete from table;
>
>         The operation seems to take in the order of hours, rather than
>         seconds
>         or minutes.
>
>         "Explain delete from table" gives me:
>
>                                   QUERY PLAN
>         ----------------------------------------------------------------
>         Seq Scan on table  (cost=0.00..3967.74 rows=115374 width=6)
>         (1 row)
>
>
>         I am using an Intel Pentium D 2.8GHz CPU. My system has about
>         1.2GB of
>         RAM. This should be ok... my database isn't that big, I think.
>
>
>         Any ideas why this takes so long and how I could speed this
>         up?
>
>         Or alternatively, is there a better way to delete all the
>         contents from
>         a table?
>
>
>         Thank you!
>
>
>
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 5: don't forget to increase your free space map settings
>
>
>
> --
> Best,
> Gourish Singbal

> Wow! That was almost instantaneous. I can't believe the difference.
>
> The only inconvenience is that I need to remove all the foreign key
> constraints before truncating, then put them back after. But I suppose
> it is a small price to pay for this incredible optimization.

    In that case, your DELETE might have been slowed down by foreign key
checks.

    Suppose you have tables A and B, and table A has a column "b_id
REFERENCES B(id)"
    When you delete from B postgres has to lookup in A which rows reference
the deleted rows in order to do the ON DELETE action you specified in the
constraint.
    If you do not have an index on b_id, this can be quite slow... so you
should check if your foreign key relations that need indexes have them.

David Leangen <postgres@leangen.net> writes:
> The only inconvenience is that I need to remove all the foreign key
> constraints before truncating, then put them back after.

I was about to ask if you had any.  Usually the reason for DELETE being
slow is that you have foreign key references to (not from) the table and
the referencing columns aren't indexed.  This forces a seqscan search
of the referencing table for each row deleted :-(

            regards, tom lane