From: Jeremy M. Guthrie <jeremy.guthrie@berbee.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor delete performance AFTER vacuum analyze
Date: Sun, 20 Jul 2003 18:51:39 -0500
Message-ID: <200307201851.39756.jeremy.guthrie@berbee.com> (raw)
In-Reply-To: <19929.1058676291@sss.pgh.pa.us>
References: <200307191622.46709.jeremy.guthrie@berbee.com>
<19929.1058676291@sss.pgh.pa.us>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I looked back at my code and I also need to reclarify something. The delete
at the end is multiple delete statements within a transaction.
After full vacuum with 160,000 records in Table: (takes a bit the first time
through)
Tlog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and
host='tbp-pp';
QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------
Index Scan using shost_idx on tlog (cost=0.00..6281.45 rows=136 width=6)
(actual time=64529.43..64529.43 rows=0 loops=1)
Index Cond: (host = 'tbp-pp'::character varying)
Filter: (tlog_id <= 47766002)
Total runtime: 64529.52 msec
After zero records in table: (
Tlog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and
host='tbp-pp';
QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------
Index Scan using shost_idx on tlog (cost=0.00..6281.45 rows=136 width=6)
(actual time=84.87..84.87 rows=0 loops=1)
Index Cond: (host = 'tbp-pp'::character varying)
Filter: (tlog_id <= 47766002)
Total runtime: 84.96 msec
Slow Explain after vacuum analyze: (this is when it gets bad)
TLog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and
shost='tbp-pp';
QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------------------
Index Scan using shost_idx on tlog (cost=0.00..6128.52 rows=82 width=6)
(actual time=262178.82..262178.82 rows=0 loops=1)
Index Cond: (host = 'tbp-pp'::character varying)
Filter: (tlog_id <= 47766002)
Total runtime: 262178.96 msec
- --
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=
=laUO
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jeremy.guthrie@berbee.com, tgl@sss.pgh.pa.us
Subject: Re: Poor delete performance AFTER vacuum analyze
In-Reply-To: <200307201851.39756.jeremy.guthrie@berbee.com>
References: <200307191622.46709.jeremy.guthrie@berbee.com> <19929.1058676291@sss.pgh.pa.us> <200307201851.39756.jeremy.guthrie@berbee.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox