Skip site navigation (1) Skip section navigation (2)

Re: INDEX BUG???

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: gabriel <gabriel(at)workingnetsp(dot)com(dot)br>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: INDEX BUG???
Date: 2001-09-03 17:43:06
Message-ID: Pine.BSF.4.21.0109031038390.76325-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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.



In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-09-03 17:57:37
Subject: Re: INDEX BUG???
Previous:From: Florian WeimerDate: 2001-09-03 16:03:37
Subject: Re: Re: Escaping strings for inclusion into SQL queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group