pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwrong count estimation in query plan
3+ messages / 2 participants
[nested] [flat]
* wrong count estimation in query plan
@ 2014-10-30 07:17 Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rural Hunter @ 2014-10-30 07:17 UTC (permalink / raw)
To: 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?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wrong count estimation in query plan
@ 2014-10-30 14:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2014-10-30 14:25 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wrong count estimation in query plan
@ 2014-10-31 13:33 Rural Hunter <ruralhunter@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Rural Hunter @ 2014-10-31 13:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
在 2014/10/30 22:25, Tom Lane 写道:
> 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
Got it. A vacuum solved the problem. Thanks.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-10-31 13:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-30 07:17 wrong count estimation in query plan Rural Hunter <ruralhunter@gmail.com>
2014-10-30 14:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-31 13:33 ` Rural Hunter <ruralhunter@gmail.com>
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