From: | Rural Hunter <ruralhunter(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | wrong count estimation in query plan |
Date: | 2014-10-30 07:17:30 |
Message-ID: | 5451E60A.60009@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-10-30 14:25:57 | Re: wrong count estimation in query plan |
Previous Message | Tom Lane | 2014-10-29 18:35:39 | Re: A local replication entry |