Postgres Pro
Downloads
Home   >   mailing lists

Thread: DELETE SQL too slow.

DELETE SQL too slow.

From
Diogo Biazus
Date:
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

Here's a part of my postgresql.conf:

max_connections = 64
sort_mem = 32168
shared_buffers = 15200
fsync = false
enable_seqscan = false
wal_buffers = 10
wal_files = 10
checkpoint_segments = 20

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

Re: DELETE SQL too slow.

From
Martijn van Oosterhout
Date:
Post EXPLAIN ANALYSE output please.

On Tue, Sep 03, 2002 at 11:24:38AM -0300, 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
>
> Here's a part of my postgresql.conf:
>
> max_connections = 64
> sort_mem = 32168
> shared_buffers = 15200
> fsync = false
> enable_seqscan = false
> wal_buffers = 10
> wal_files = 10
> checkpoint_segments = 20
>
> --
> Diogo de Oliveira Biazus
> diogo@ikono.com.br
> Ikono Sistemas e Automação
> http://www.ikono.com.br
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: DELETE SQL too slow.

From
"Ben-Nes Michael"
Date:
First try to VACUM ANALYZE.
if this dont help, try to drop the index and recrate it ?

also do you have enugh ram ?

> 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
>
> Here's a part of my postgresql.conf:
>
> max_connections = 64
> sort_mem = 32168
> shared_buffers = 15200
> fsync = false
> enable_seqscan = false
> wal_buffers = 10
> wal_files = 10
> checkpoint_segments = 20
>
> --
> Diogo de Oliveira Biazus
> diogo@ikono.com.br
> Ikono Sistemas e Automação
> http://www.ikono.com.br
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: DELETE SQL too slow.

From
Tom Lane
Date:
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

Re: DELETE SQL too slow.

From
Stephan Szabo
Date:
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.

Re: DELETE SQL too slow.

From
Diogo Biazus
Date:
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

Re: DELETE SQL too slow.

From
Stephan Szabo
Date:
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?