Re: [PERFORM] Poor delete performance AFTER vacuum analyze
"Jeremy M. Guthrie" <[EMAIL PROTECTED]> writes: > 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. I think you are going to have to show us all the details of what you're doing in between these queries. I was expecting to see a difference in query plans, but you've got the exact same plan in all three runs --- so it's not the planner making the difference here, nor the ANALYZE statistics. My best conjecture now is something odd about the way you are deleting the old data or loading the new. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] 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- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Poor delete performance AFTER vacuum analyze
On Sat, 19 Jul 2003, Jeremy M. Guthrie wrote: > 100megs of new data each day. However, the instant the system finishes only > a 'vacuum analyze', the whole thing slows WAY down to where each run can take > 10-15 minutes. Have you run EXPLAIN ANALYZE on the delete query before and after the vacuum? Does it explain why it goes slower? -- /Dennis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Poor delete performance AFTER vacuum analyze
"Jeremy M. Guthrie" <[EMAIL PROTECTED]> writes: > My system will run great after a full vacuum(as I would expect). It will run > all day long taking only 3-5 seconds to run and deal with approximately > 100megs of new data each day. However, the instant the system finishes only > a 'vacuum analyze', the whole thing slows WAY down to where each run can take > 10-15 minutes. Could we see EXPLAIN ANALYZE for the deletion query in both the fast and slow states? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
