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

3 Sep 2001 13:08:40 -0000
"gabriel" <gabriel@workingnetsp.com.br>
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 ??

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.



Mon, 3 Sep 2001 10:43:06 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: INDEX BUG???
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


Mon, 3 Sep 2001 19:57:37 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: INDEX BUG???