From: Rural Hunter <ruralhunter@gmail.com>
Message-ID: <5451E60A.60009@gmail.com>
Date: Thu, 30 Oct 2014 15:17:30 +0800
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: wrong count estimation in query plan
Content-Type: text/plain; charset=gb18030; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi,

I have a table with very frequent updates while inserts and deletes are 
very few. Recently I noticed the count estimation of the table in query 
plan are far wrong. This is what I found:

# select count(*) from users;
  count
-------
   1116
(1 row)

testdb=# explain select * from users;
                           QUERY PLAN
---------------------------------------------------------------
  Seq Scan on users  (cost=0.00..34456.68 rows=73468 width=625)
(1 row)

testdb=# vacuum analyze users;
VACUUM
testdb=# explain select * from users;
                           QUERY PLAN
---------------------------------------------------------------
  Seq Scan on users  (cost=0.00..34705.32 rows=97332 width=624)
(1 row)

testdb=# analyze users;
ANALYZE
testdb=# explain select * from users;
                           QUERY PLAN
---------------------------------------------------------------
  Seq Scan on users  (cost=0.00..33857.43 rows=11743 width=625)
(1 row)

testdb=# analyze users;
ANALYZE
testdb=# explain select * from users;
                           QUERY PLAN
--------------------------------------------------------------
  Seq Scan on users  (cost=0.00..33765.88 rows=2288 width=648)
(1 row)

testdb=# analyze users;
\ANALYZE
testdb=# explain select * from users;
                           QUERY PLAN
--------------------------------------------------------------
  Seq Scan on users  (cost=0.00..33778.94 rows=1294 width=625)
(1 row)

testdb=# analyze users;
ANALYZE
testdb=# explain select * from users;
                           QUERY PLAN
--------------------------------------------------------------
  Seq Scan on users  (cost=0.00..33777.19 rows=1119 width=624)
(1 row)

My question is, why I had to run analyze so many times to make the 
estimation be close with the actual count?


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin