From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: wrong count estimation in query plan
Date: Thu, 30 Oct 2014 10:25:57 -0400
Message-ID: <19884.1414679157@sss.pgh.pa.us> (raw)
In-Reply-To: <5451E60A.60009@gmail.com>
References: <5451E60A.60009@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Rural Hunter <ruralhunter@gmail.com> writes:
> My question is, why I had to run analyze so many times to make the
> estimation be close with the actual count?
If ANALYZE doesn't scan the entire table then it updates the rowcount
estimate using a moving-average approach, rather than assuming that
the blocks it did look at are necessarily completely representative.
That causes the estimate to converge over multiple passes. Since
it took so many cycles to get close to reality, we can conclude that
the table is several times larger than the number of blocks ANALYZE
is willing to sample ... which, for a table with only 1000 rows in
it, says you've got a bad table-bloat problem. VACUUM FULL or CLUSTER
might be indicated.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ruralhunter@gmail.com
Subject: Re: wrong count estimation in query plan
In-Reply-To: <19884.1414679157@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