From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-admin@postgresql.org
Subject: wrong count estimation in query plan
Date: Thu, 30 Oct 2014 15:17:30 +0800
Message-ID: <5451E60A.60009@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
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: ruralhunter@gmail.com
Subject: Re: wrong count estimation in query plan
In-Reply-To: <5451E60A.60009@gmail.com>
* 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