Re: Poor delete performance AFTER vacuum analyze

-----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-----