Postgres Pro
Downloads
Home   >   mailing lists

Thread: Screwy Statistics...

Screwy Statistics...

From
"Glen Parker"
Date:
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

Re: Screwy Statistics...

From
Darren Ferguson
Date:
VACUUM ANALYZE then do the same you will get the results you are looking
for.

Darren
On Fri, 6 Sep 2002, Glen Parker wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Darren Ferguson

Re: Screwy Statistics...

From
Stephan Szabo
Date:
On Fri, 6 Sep 2002, Glen Parker wrote:

> 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.

Do an explain analyze and set enable_seqscan=off
and see which runs faster which is pretty much the only
way to be sure, but if there's no clustering,
6700 rows of 35000 rows with an expected width of 520
probably touches close to every page in the heap file any
way (figure that there's about 2-3k pages barring
dead tuples or such).