Обсуждение: Screwy Statistics...

First, shouldn't statistics have some clue how many rows might be in the
table, at least right after an analyze?

Second, if the planner believes it will find 6700 rows in a 35000 row
table, shouldn't it choose an index?  I have a unique index on "branch".
What percentage of expected rows/possible rows is the usual cutoff,
anyone know?  Whatever it is, it seems too low to me.


oms=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

oms=# analyze items;
ANALYZE

oms=# select count(*) from items;
 count
-------
 34865
(1 row)

oms=# explain select * from items where branch='10';
NOTICE:  QUERY PLAN:

Seq Scan on items  (cost=0.00..3588.70 rows=46546 width=520)

EXPLAIN

oms=# explain select * from items where branch='30';
NOTICE:  QUERY PLAN:

Seq Scan on items  (cost=0.00..3588.70 rows=6710 width=520)

EXPLAIN


Glen Parker
glenebob@nwlink.com

"Glen Parker" <glenebob@nwlink.com> writes:
> First, shouldn't statistics have some clue how many rows might be in the
> table, at least right after an analyze?

Only approximately -- VACUUM or VACUUM ANALYZE will generate an
exact-as-of-the-moment reltuples entry, but just ANALYZE doesn't,
because it doesn't do a complete table scan.

We have an open bug report about ANALYZE's guesstimate sometimes being
further off than one would like.  This happens because it estimates
the entire file's tuples-per-page fill factor from that of the first
few pages, and of course that could be radically wrong.

            regards, tom lane