Re: RES: [SQL] Queries not using Index

On Wed, 24 Jul 2002, Daryl Herzmann wrote:
[lots of chopping and rearranging...]
> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> Seq Scan on t2002_06 (cost=0.00..35379.69)
> Total runtime: 2452.14 msec
>
> snet=# set enable_seqscan=off;
> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> Index Scan using t2002_06_station_hash_idx on t2002_06
> (cost=0.00..132190.93)
> Total runtime: 325.22 msec

I don't know how these indexes actually work, but just looking at the
numbers here, it uses a seq scan because it thinks a seq scan costs far
less than an index scan (35379 v 132190) even though the actual runtime is
much less for the index scan (2452 msec v 325 msec).

*why* it's guessing wrong, I haven't got a clue. =)

--
Phil Davey
Computer Officer
Hughes Hall College, Cambridge
Email phil(dot)davey(at)hughes(dot)cam(dot)ac(dot)uk