DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=b9l6VrsUxI0s3mi1ZDLdlesdP+zjr7hmurVK9Sbbv0SMr5ytVx7bcqSRAkZGtf8FClMPXkCCdpkGj8wCDTZktKulUeg1B6qKqsLQVBX2FSmK+f7d5rD2TX8D6z5IEhte9IkmnpS7hE9DLUqIVbipfgHLfVyLL1ADuowp0U1Zafk=;
Message-ID: <821768.44263.qm@web39705.mail.mud.yahoo.com>
Date: Wed, 12 May 2010 20:13:42 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Slow Bulk Delete
To: pgsql-performance@postgresql.org, thilo <thilo.tanner@gmail.com>
In-Reply-To: 
 <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Thilo,=0A=0AJust a few of thoughts off the top of my head:=0A=0A1.  If you =
know the ids of the rows you want to delete beforhand, insert them in a tab=
le, then run the delete based on a join with this table.=0A=0A2.  Better ye=
t, insert the ids into a table using COPY, then use a join to create a new =
table with the rows you want to keep from the first table.  Drop the origin=
al source table, truncate the id table, rename the copied table and add ind=
exes and constraints.=0A=0A3.  See if you can partition the table somehow s=
o the rows you want to delete are in a single partitioned child table.  Whe=
n its time to delete them just drop the child table.=0A=0AOf course, if the=
 1M rows you need to delete is very small compared to the total overall siz=
e of the original table the first two techniques might now buy you anything=
, but its worth a try.=0A=0AGood luck!=0A=0ABob Lunney=0A=0A--- On Sat, 5/8=
/10, thilo <thilo.tanner@gmail.com> wrote:=0A=0A> From: thilo <thilo.tanner=
@gmail.com>=0A> Subject: [PERFORM] Slow Bulk Delete=0A> To: pgsql-performan=
ce@postgresql.org=0A> Date: Saturday, May 8, 2010, 7:39 AM=0A> Hi all!=0A> =
=0A> We moved from MySQL to Postgresql for some of our projects.=0A> So far=
=0A> we're very impressed with the performance (especially=0A> INSERTs and=
=0A> UPDATEs), except for a strange problem with the following=0A> bulk del=
ete=0A> query:=0A> =0A> DELETE FROM table1 WHERE table2_id =3D ?=0A> =0A> I=
 went through these Wiki pages, trying to solve the=0A> problem:=0A> =0A> h=
ttp://wiki.postgresql.org/wiki/SlowQueryQuestions and=0A> http://wiki.postg=
resql.org/wiki/Performance_Optimization=0A> =0A> but unfortunately without =
much luck.=0A> =0A> Our application is doing batch jobs. On every batch run=
, we=0A> must=0A> delete approx. 1M rows in table1 and recreate these=0A> e=
ntries. The=0A> inserts are very fast, but deletes are not. We cannot make=
=0A> updates,=0A> because there's no identifying property in the objects of=
=0A> table1.=0A> =0A> This is what EXPLAIN is telling me:=0A> =0A> EXPLAIN =
ANALYZE DELETE FROM table1 WHERE table2_id =3D=0A> 11242939=0A> =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0=A0=A0QUERY=0A> PLAN=0A> -----------=
---------------------------------------------------------------------------=
--------------------------------------=0A>  Index Scan using sr_index on ta=
ble1=A0 (cost=3D0.00..8.56=0A> rows=3D4 width=3D6)=0A> (actual time=3D0.111=
..0.154 rows=3D4 loops=3D1)=0A> =A0=A0=A0Index Cond: (table2_id =3D 1124293=
9)=0A>  Total runtime: 0.421 ms=0A> (3 rows)=0A> =0A> This seems to be very=
 fast (using the index), but running=0A> this query=0A> from JDBC takes up =
to 20ms each. For 1M rows this sum up to=0A> several=0A> hours. When I have=
 a look at pg_top psql uses most of the=0A> time for the=0A> deletes. CPU u=
sage is 100% (for the core used by=0A> postgresql). So it=0A> seems that po=
stgresql is doing some sequential scanning or=0A> constraint=0A> checks.=0A=
> =0A> This is the table structure:=0A> =0A> id=A0=A0=A0=0A> bigint=A0=A0=
=A0=A0=A0(primary key)=0A> table2_id=A0=A0=A0=0A> bigint=A0=A0=A0=A0=A0(for=
eign key constraint=0A> to table 2, *indexed*)=0A> table3_id=A0=A0=A0=0A> b=
igint=A0=A0=A0=A0=A0(foreign key constraint=0A> to table 3, *indexed*)=0A> =
some non-referenced text and boolean fields=0A> =0A> My server settings (Po=
tgresql 8.4.2):=0A> =0A> shared_buffers =3D 1024MB=0A> effective_cache_size=
 =3D 2048MB=0A> work_mem =3D 128MB=0A> wal_buffers =3D 64MB=0A> checkpoint_=
segments =3D 32=0A> checkpoint_timeout =3D 15min=0A> checkpoint_completion_=
target =3D 0.9=0A> =0A> It would be very nice to give me a hint to solve th=
e=0A> problem. It=0A> drives me crazy ;-)=0A> =0A> If you need more details=
 please feel free to ask!=0A> =0A> Thanks in advance for your help!=0A> =0A=
> Kind regards=0A> =0A> Thilo=0A> =0A> -- =0A> Sent via pgsql-performance m=
ailing list (pgsql-performance@postgresql.org)=0A> To make changes to your =
subscription:=0A> http://www.postgresql.org/mailpref/pgsql-performance=0A> =
=0A=0A=0A