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