Message-ID: <4BE56459.4040007@squeakycode.net>
Date: Sat, 08 May 2010 08:17:13 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: thilo <thilo.tanner@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Slow Bulk Delete
References: <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
In-Reply-To: 
 <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/08/2010 06:39 AM, thilo wrote:
> Hi all!
>
> We moved from MySQL to Postgresql for some of our projects. So far
> we're very impressed with the performance (especially INSERTs and
> UPDATEs), except for a strange problem with the following bulk delete
> query:
>
> DELETE FROM table1 WHERE table2_id = ?
>
> I went through these Wiki pages, trying to solve the problem:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions and
> http://wiki.postgresql.org/wiki/Performance_Optimization
>
> but unfortunately without much luck.
>
> Our application is doing batch jobs. On every batch run, we must
> delete approx. 1M rows in table1 and recreate these entries. The
> inserts are very fast, but deletes are not. We cannot make updates,
> because there's no identifying property in the objects of table1.
>
> This is what EXPLAIN is telling me:
>
> EXPLAIN ANALYZE DELETE FROM table1 WHERE table2_id = 11242939
>                                                           QUERY
> PLAN
> ----------------------------------------------------------------------------------------------------------------------------
>   Index Scan using sr_index on table1  (cost=0.00..8.56 rows=4 width=6)
> (actual time=0.111..0.154 rows=4 loops=1)
>     Index Cond: (table2_id = 11242939)
>   Total runtime: 0.421 ms
> (3 rows)
>
> This seems to be very fast (using the index), but running this query
> from JDBC takes up to 20ms each. For 1M rows this sum up to several
> hours. When I have a look at pg_top psql uses most of the time for the
> deletes. CPU usage is 100% (for the core used by postgresql). So it
> seems that postgresql is doing some sequential scanning or constraint
> checks.
>
> This is the table structure:
>
> id	bigint	 (primary key)
> table2_id	bigint	 (foreign key constraint to table 2, *indexed*)
> table3_id	bigint	 (foreign key constraint to table 3, *indexed*)
> some non-referenced text and boolean fields
>
> My server settings (Potgresql 8.4.2):
>
> shared_buffers = 1024MB
> effective_cache_size = 2048MB
> work_mem = 128MB
> wal_buffers = 64MB
> checkpoint_segments = 32
> checkpoint_timeout = 15min
> checkpoint_completion_target = 0.9
>
> It would be very nice to give me a hint to solve the problem. It
> drives me crazy ;-)
>
> If you need more details please feel free to ask!
>
> Thanks in advance for your help!
>
> Kind regards
>
> Thilo


I am going to guess the slow part is sending 1M different queries back and forth from client to server.  You could try batching them together:

DELETE FROM table1 WHERE table2_id in (11242939, 1,2,3,4,5...., 42);

Also are you preparing the query?

-Andy