Re: query plan question - Mailing list pgsql-performance

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