X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 57E33D1B4EB for ; Wed, 17 Sep 2003 20:20:14 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 95101-02 for ; Wed, 17 Sep 2003 17:20:04 -0300 (ADT) Received: from wight.ymogen.net (unknown [217.27.240.153]) by svr1.postgresql.org (Postfix) with SMTP id BAC23D1B4E1 for ; Wed, 17 Sep 2003 17:20:01 -0300 (ADT) Received: (qmail 10191 invoked from network); 17 Sep 2003 20:20:03 -0000 Received: from dsl-217-155-239-51.zen.co.uk (HELO finisterre) (217.155.239.51) by wight.ymogen.net with SMTP; 17 Sep 2003 20:20:03 -0000 From: "Matt Clark" To: "scott.marlowe" Cc: Subject: Re: Is there a reason _not_ to vacuum continuously? Date: Wed, 17 Sep 2003 21:20:02 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-reply-to: Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/249 X-Sequence-Number: 3615 Yes, that makes sense. My worry is really the analyzes. I gather/imagine that: 1) Indexes on fields that are essentially random gain little from being analyzed. 2) Fields that increase monotonically with insertion order have a problem with index growth in 7.2. There may be a performance issue connected with this, although indexes on these fields also gain little from analysis. So if I can't vacuum full I'm SOL anyway and should upgrade to 7.4.1 when available? Further data: When I run a vacuum analyze my app servers do see an increase in response time from PG, even though the DB server is under no more apparent load. I can only assume some kind of locking issue. Is that fair? M > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of > scott.marlowe > Sent: 17 September 2003 20:55 > To: Matt Clark > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Is there a reason _not_ to vacuum continuously? > > > On Wed, 17 Sep 2003, Matt Clark wrote: > > > *** THE QUESTION(S) *** > > Is there any reason for me not to run continuous sequential > vacuum analyzes? > > At least for the 6 tables that see a lot of updates? > > I hear 10% of tuples updated as a good time to vac-an, but does > my typical > > count of 3 indexes per table affect that? > > Generally, the only time continuous vacuuming is a bad thing is when you > are I/O bound. If you are CPU bound, then continuous vacuuming > is usually > acceptable. > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >