Обсуждение: 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
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
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).