Thread: INDEX BUG???

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

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

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