X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 63A7E9F931D for ; Mon, 8 May 2006 16:48:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 82900-05 for ; Mon, 8 May 2006 16:48:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9BD409F931E for ; Mon, 8 May 2006 16:48:42 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k48JmcDT029127; Mon, 8 May 2006 15:48:39 -0400 (EDT) To: Jan de Visser cc: pgsql-performance@postgresql.org, "Matthew T. O'Connor" Subject: Re: Query runs 38 seconds for small database! In-reply-to: <200605081519.39293.jdevisser@digitalfairway.com> References: <20060508174604.GX99570@pervasive.com> <200605081519.39293.jdevisser@digitalfairway.com> Comments: In-reply-to Jan de Visser message dated "Mon, 08 May 2006 15:19:39 -0400" Date: Mon, 08 May 2006 15:48:38 -0400 Message-ID: <29126.1147117718@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/151 X-Sequence-Number: 18938 Jan de Visser writes: > On Monday 08 May 2006 14:10, Andrus wrote: >> I created empty table konto and loaded more that 219 records to it during >> database creation. >> So it seems that if table grows from zero to more than 219 times larger >> then it was still not processed. > That's because you need at least 500 rows for analyze and 100 for a vacuum, > (autovacuum_vacuum_threshold = 1000, autovacuum_analyze_threshold = 500). This crystallizes something that's been bothering me for awhile, actually: why do the "threshold" variables exist at all? If we took them out, or at least made their default values zero, then the autovac criteria would simply be "vacuum or analyze if at least X% of the table has changed" (where X is set by the "scale_factor" variables). Which seems intuitively reasonable. As it stands, the thresholds seem to bias autovac against ever touching small tables at all ... but, as this example demonstrates, a fairly small table can still kill your query performance if the planner knows nothing about it. regards, tom lane