X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 4D100D1C50A for ; Sun, 20 Jul 2003 23:51:39 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 84495-01 for ; Sun, 20 Jul 2003 23:51:38 +0000 (GMT) Received: from ctg-msnex01.staff.berbee.com (msn-office1.binc.net [64.73.12.254]) by svr1.postgresql.org (Postfix) with ESMTP id BAB5DD1C509 for ; Sun, 20 Jul 2003 20:51:37 -0300 (ADT) Received: from localhost ([172.30.254.220] RDNS failed) by ctg-msnex01.staff.berbee.com with Microsoft SMTPSVC(5.0.2195.5329); Sun, 20 Jul 2003 18:51:40 -0500 From: "Jeremy M. Guthrie" Reply-To: jeremy.guthrie@berbee.com Organization: Berbee Information Networks To: Tom Lane Subject: Re: Poor delete performance AFTER vacuum analyze Date: Sun, 20 Jul 2003 18:51:39 -0500 User-Agent: KMail/1.5.1 Cc: pgsql-performance@postgresql.org References: <200307191622.46709.jeremy.guthrie@berbee.com> <19929.1058676291@sss.pgh.pa.us> In-Reply-To: <19929.1058676291@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200307201851.39756.jeremy.guthrie@berbee.com> X-OriginalArrivalTime: 20 Jul 2003 23:51:40.0933 (UTC) FILETIME=[DDBB6B50:01C34F19] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/315 X-Sequence-Number: 2608 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I looked back at my code and I also need to reclarify something. The delet= e=20 at the end is multiple delete statements within a transaction. After full vacuum with 160,000 records in Table: (takes a bit the first ti= me=20 through) Tlog=3D# explain analyze delete from Tlog where Tlog_ID <=3D 47766002 and= =20 host=3D'tbp-pp'; QUERY PLAN - -------------------------------------------------------------------------= ---------------------------------------------------- Index Scan using shost_idx on tlog (cost=3D0.00..6281.45 rows=3D136 width= =3D6)=20 (actual time=3D64529.43..64529.43 rows=3D0 loops=3D1) Index Cond: (host =3D 'tbp-pp'::character varying) Filter: (tlog_id <=3D 47766002) Total runtime: 64529.52 msec After zero records in table: ( Tlog=3D# explain analyze delete from Tlog where Tlog_ID <=3D 47766002 and= =20 host=3D'tbp-pp';=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 QUERY PLAN - -------------------------------------------------------------------------= ---------------------------------------------- Index Scan using shost_idx on tlog (cost=3D0.00..6281.45 rows=3D136 width= =3D6)=20 (actual time=3D84.87..84.87 rows=3D0 loops=3D1) Index Cond: (host =3D 'tbp-pp'::character varying) Filter: (tlog_id <=3D 47766002) Total runtime: 84.96 msec Slow Explain after vacuum analyze: (this is when it gets bad) TLog=3D# explain analyze delete from Tlog where Tlog_ID <=3D 47766002 and= =20 shost=3D'tbp-pp'; QUERY PLAN - -------------------------------------------------------------------------= ----------------------------------------------------- Index Scan using shost_idx on tlog (cost=3D0.00..6128.52 rows=3D82 width= =3D6)=20 (actual time=3D262178.82..262178.82 rows=3D0 loops=3D1) Index Cond: (host =3D 'tbp-pp'::character varying) Filter: (tlog_id <=3D 47766002) Total runtime: 262178.96 msec - --=20 Jeremy M. Guthrie Systems Engineer Berbee 5520 Research Park Dr. Madison, WI 53711 Phone: 608-298-1061 Berbee...Decade 1. 1993-2003 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/GysLqtjaBHGZBeURAhNTAJ0QA2/eZM/DhSyxmXi89i6kXFQFwgCfacZY UIMUdK95O3N0UpOTxedM6Pw=3D =3DlaUO -----END PGP SIGNATURE-----