Skip site navigation (1) Skip section navigation (2)

Re: VACUUM and ANALYZE Follow-Up

From: "Mark Dexter" <MDEXTER(at)dexterchaney(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: VACUUM and ANALYZE Follow-Up
Date: 2004-11-30 17:40:47
Message-ID: 5E8F9F5B63726C48836757FE673B584E01215AA4@dcimail.dexterchaney.local (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Tom, I did read through the links you provided.  Unfortunately, I don't
feel qualified to judge the technical merits of the possible solutions.
Since you appear to be well informed on this issue, can I ask you a
couple of quick questions?

1. Would it be difficult to add an option to ANALYZE to force it to
pretend that there are a minimum number of rows (e.g., ANALYZE MINIMUM
1000 or something)?  This would appear to be a simple-minded way to
solve the problem without any concerns about backward compatibility.

2. Why does a newly CREATE'd table behave differently than an empty
table after ANALYZE?  Does it make sense that it should?  In the CREATE
case, the assumptions appear to be much more reasonable for a table that
is going to grow.  

3. Has anyone ever tested whether there is a measurable performance
gained after doing ANALYZE on empty or nearly empty tables?  We know
that there is a very large (in my case 15x) performance loss when the
table starts growing.  If the gain is small or negligable when the
tables really are small, then perhaps worrying about maintaining current
behaviour is not as important.

The nice thing about option (1) is that is solves the slow insert issue
both for empty tables and for tables with a few rows.  It also causes
absolutely no backward-compatibility issues.

Thanks very much for your comments on this.  Mark


pgsql-general by date

Next:From: Net Virtual Mailing ListsDate: 2004-11-30 17:46:02
Subject: Re: [ANNOUNCE] USENET vs Mailing Lists Poll ...
Previous:From: Richard HuxtonDate: 2004-11-30 17:13:59
Subject: Re: starting the database server

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group