pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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-----




view thread (5+ messages)  latest in thread

Message-ID: <200307201851.39756.jeremy.guthrie@berbee.com>
Permalink:  ../200307201851.39756.jeremy.guthrie@berbee.com/
Also on:    postgresql.org/message-id/200307201851.39756.jeremy.guthrie@berbee.com

 · 

reply

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