DELETE SQL too slow.

Diogo Biazus <diogo@ikono.com.br> writes:

I'm having problems with delete commands in postgres. They are too slow.
I'm trying to delete a set o 300 rows in table with 50000 rows with a
simple command like:

DELETE FROM table WHERE field1 = '4'

When I run the explain it tells me that index_scan is being used. But to
complete the delete it takes more than 30 minutes and the CPU use never
rise above 10%.

I'm betting this table is referenced by foreign key constraints on other
tables, and you are missing some needed indexes on those other tables.

regards, tom lane

First try to VACUM ANALYZE.
if this dont help, try to drop the index and recrate it ?

also do you have enugh ram ?

On Tue, 3 Sep 2002, Diogo Biazus wrote:

I'm having problems with delete commands in postgres. They are too slow.
I'm trying to delete a set o 300 rows in table with 50000 rows with a
simple command like:

DELETE FROM table WHERE field1 = '4'

When I run the explain it tells me that index_scan is being used. But to
complete the delete it takes more than 30 minutes and the CPU use never
rise above 10%. The server that runs postgres is a RedHat Linux 7.3 over
a Pentium III 900 Mhz with 512 MB RAM

Are you sure that there isn't a foreign key referencing this table?
That'll cascade out to at least a bunch of selects, and if you have
an on delete action, that'll change the other table(s) and possibly
then any table that references that one.

I already have ran VACUUM ANALYSE
And I used the REINDEX too.

Here is the explain:

NOTICE: QUERY PLAN:

Index Scan using palavrasbusca_codbusca_ix on palavrasbusca
(cost=0.00..333.43 rows=443 width=6)

My SQL is still running, it been more than one hour.

The table has a reference to one other table and it has an index.

First try to VACUM ANALYZE.
if this dont help, try to drop the index and recrate it ?

also do you have enugh ram ?

--
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automa��o
http://www.ikono.com.br

On Tue, 3 Sep 2002, Diogo Biazus wrote:

I already have ran VACUUM ANALYSE
And I used the REINDEX too.

Here is the explain:

NOTICE: QUERY PLAN:

Index Scan using palavrasbusca_codbusca_ix on palavrasbusca
(cost=0.00..333.43 rows=443 width=6)

My SQL is still running, it been more than one hour.

The table has a reference to one other table and it has an index.

I'd guess it's related to foreign keys, but it'd be helpful to
see the statements used to create the tables and constraints/indexes
(or at least a pg_dump of those with the create index/create constraint
trigger).

Also, are there any other triggers on the table?