X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 867693A4395 for ; Wed, 17 Nov 2004 19:04:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79706-01 for ; Wed, 17 Nov 2004 19:04:00 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 695A73A4346 for ; Wed, 17 Nov 2004 19:03:59 +0000 (GMT) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=[192.168.0.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.42) id 1CUV5v-0008D7-FY; Wed, 17 Nov 2004 14:03:55 -0500 Message-ID: <419BA01C.7000903@zeut.net> Date: Wed, 17 Nov 2004 14:01:48 -0500 From: "Matthew T. O'Connor" Organization: Terrie O'Connor Realtors User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: David Parker Cc: Tom Lane , Jeff , Russell Smith , pgsql-performance@postgresql.org Subject: Re: query plan question References: <07FDEE0ED7455A48AC42AC2070EDFF7C26BA10@corpsrv2.tazznetworks.com> In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C26BA10@corpsrv2.tazznetworks.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.80.199.173 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/259 X-Sequence-Number: 9213 Well based on the autovacuum log that you attached, all of those tables are insert only (at least during the time period included in the log. Is that correct? If so, autovacuum will never do a vacuum (unless required by xid wraparound issues) on those tables. So this doesn't appear to be an autovacuum problem. I'm not sure about the missing pg_statistic entries anyone else care to field that one? Matthew David Parker wrote: >Thanks. The tables I'm concerned with are named: 'schema', 'usage', >'usageparameter', and 'flow'. It looks like autovacuum is performing >analyzes: > >% grep "Performing: " logs/.db.tazz.vacuum.log >[2004-11-17 12:05:58 PM] Performing: ANALYZE >"public"."scriptlibrary_library" >[2004-11-17 12:15:59 PM] Performing: ANALYZE >"public"."scriptlibraryparm" >[2004-11-17 12:15:59 PM] Performing: ANALYZE "public"."usageparameter" >[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageproperty" >[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."route" >[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageparameter" >[2004-11-17 12:21:00 PM] Performing: ANALYZE >"public"."scriptlibrary_library" >[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usage" >[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usageparameter" >[2004-11-17 12:31:04 PM] Performing: ANALYZE "public"."usageproperty" >[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."route" >[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."service_usage" >[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."usageparameter" > >But when I run the following: > >select * from pg_statistic where starelid in >(select oid from pg_class where relname in >('schema','usageparameter','flow','usage')) > >it returns no records. Shouldn't it? It doesn't appear to be doing a >vacuum anywhere, which makes sense because none of these tables have >over the default threshold of 1000. Are there statistics which only get >generated by vacuum? > >I've attached a gzip of the pg_autovacuum log file, with -d 3. > >Thanks again. > >- DAP > > > > >>-----Original Message----- >>From: Matthew T. O'Connor [mailto:matthew@zeut.net] >>Sent: Wednesday, November 17, 2004 11:41 AM >>To: David Parker >>Cc: Tom Lane; Jeff; Russell Smith; pgsql-performance@postgresql.org >>Subject: Re: [PERFORM] query plan question >> >>David Parker wrote: >> >> >> >>>We're using postgresql 7.4.5. I've only recently put pg_autovacuum in >>>place as part of our installation, and I'm basically taking the >>>defaults. I doubt it's a problem with autovacuum itself, but rather >>>with my configuration of it. I have some reading to do, so >>> >>> >>any pointers >> >> >>>to existing autovacuum threads would be greatly appreciated! >>> >>> >>> >>Well the first thing to do is increase the verbosity of the >>pg_autovacuum logging output. If you use -d2 or higher, >>pg_autovacuum will print out a lot of detail on what it thinks >>the thresholds are and >>why it is or isn't performing vacuums and analyzes. Attach >>some of the >>log and I'll take a look at it. >> >> >>