Thread

1. INDEX BUG???

   gabriel <gabriel@workingnetsp.com.br> — 2001-09-03T13:08:40Z

   hello All
   
   I tried the following commands:
   ponto=# explain select * from horarios where funcionario>10000;
   NOTICE:  QUERY PLAN:
   
   Seq Scan on horarios  (cost=0.00..176.21 rows=2432 width=132)
   
   EXPLAIN
   ponto=# explain select * from horarios where funcionario=10000;
   NOTICE:  QUERY PLAN:
   
   Index Scan using horarios_func_data on horarios  (cost=0.00..55.37 rows=73 
   width=132)
   
   EXPLAIN
   
   So my question is why in the first case the postgre did'nt use the index 
   and made a seq scan ??
   
   thanks and sorry about my english...
   
   

2. Re: INDEX BUG???

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-09-03T17:43:06Z

   > hello All
   > 
   > I tried the following commands:
   > ponto=# explain select * from horarios where funcionario>10000;
   > NOTICE:  QUERY PLAN:
   > 
   > Seq Scan on horarios  (cost=0.00..176.21 rows=2432 width=132)
   > 
   > EXPLAIN
   > ponto=# explain select * from horarios where funcionario=10000;
   > NOTICE:  QUERY PLAN:
   > 
   > Index Scan using horarios_func_data on horarios  (cost=0.00..55.37 rows=73 
   > width=132)
   > 
   > EXPLAIN
   > 
   > So my question is why in the first case the postgre did'nt use the index 
   > and made a seq scan ??
   
   In the first case it estimates 2432 rows returned, in the second it
   estimates 73 rows.  How big is the table in question?  Have you vacuum
   analyzed recently?  Are those reasonable estimates? (ie, what would
   a select count(*) show for those two conditions)
   
   At some point, the cost of doing the index scan exceeds that of the seq
   scan because the index scan requires reading the heap file in random
   order so that we know if the tuple is visible to the selecting
   transaction (in addition to the reading of the index itself).  If it's
   choosing the wrong plan that usually means the estimates are off.
   
   
   
   

3. Re: INDEX BUG???

   Peter Eisentraut <peter_e@gmx.net> — 2001-09-03T17:57:37Z

   gabriel writes:
   
   > So my question is why in the first case the postgre did'nt use the index
   > and made a seq scan ??
   
   Because it thinks the sequential scan will be faster.  You didn't show any
   evidence to the contrary.
   
   -- 
   Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter