From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan de Visser <jdevisser@digitalfairway.com>
Cc: pgsql-performance@postgresql.org, "Matthew T. O'Connor" <matthew@zeut.net>
Subject: Re: Query runs 38 seconds for small database!
Date: Mon, 08 May 2006 15:48:38 -0400
Message-ID: <29126.1147117718@sss.pgh.pa.us> (raw)
In-Reply-To: <200605081519.39293.jdevisser@digitalfairway.com>
References: <e3n8at$1f7i$1@news.hub.org>
<20060508174604.GX99570@pervasive.com>
<e3o1ib$1ik3$1@news.hub.org>
<200605081519.39293.jdevisser@digitalfairway.com>
Jan de Visser <jdevisser@digitalfairway.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jdevisser@digitalfairway.com, matthew@zeut.net
Subject: Re: Query runs 38 seconds for small database!
In-Reply-To: <29126.1147117718@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox